SQL Dev Query with Joins

Question

As an exercise, I am creating a database for a zoo. I have an Animal table, Zoo Location ZLoc and a Moving record Mrec table. The Zloc table has an attribute called region. I am trying to SELECT all animals that are in the 'North' region. Here is what I try, but it doesn't produce the expected results:

SELECT 
    nickname, region 
FROM 
    Animal 
    JOIN MRec USING (Animal_ID) 
    JOIN ZLoc USING (ZLoc_ID)
WHERE region IN ‘North’;

nickname is the attribute for animals name. Mrec acts as the associative between animal and Zloc.

To clarify I am using Oracle SQL Developer And I have made sure the single 'quotes' are correct

For starters, your quotes around North are wrong. Use normal single-quotes like 'North' — Phil
– Phil, Commented Nov 26, 2013 at 22:21
Don't use USING. I know it's cool, but it gets so confusing on more complex queries. — Strawberry
– Strawberry, Commented Nov 26, 2013 at 22:22
The error is: Error starting at line 2 in command: SELECT nickname, region FROM Animal JOIN MRec USING (Animal_ID) JOIN ZLoc USING (ZLoc_ID) WHERE zloc.region IN 'North' Error at Command Line:3 Column:7 Error report: SQL Error: ORA-00904: "ZLOC"."REGION": invalid identifier 00904. 00000 - "%s: invalid identifier" Thank you for the advice so far! — user3038436
– user3038436, Commented Nov 26, 2013 at 22:23

Explosion Pills · Accepted Answer · 2013-11-26 22:22:22Z

1

IN requires parentheses as in IN ('North'). Also make sure you are using apostrophes rather than multibyte curly quotes.

answered Nov 26, 2013 at 22:22

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3038436 Over a year ago

I have just tried this and I am still getting an "invalid identifier" error. I do think you are correct with the parenthesis though! Thank you!

Gayathri · Accepted Answer · 2013-11-27 23:24:48Z

1

As the error message states, the issue is with the table structure itself, SQL Error: ORA-00904: "ZLOC"."REGION": invalid identifier 00904. 00000 - "%s: invalid identifier"

Which implies column: Zloc->region is not available.

ORA-00904: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

answered Nov 27, 2013 at 23:24

Gayathri

9126 silver badges19 bronze badges

Comments

Smeghead Sev · Accepted Answer · 2013-11-26 22:34:23Z

0

I'm not positive about your data / table setup but to select all the animals from the region(loc_id) north this should do the trick. It will select all the animals then joins on the move table (MRec) and then joins onto the location table (ZLoc). You may have to update the names of the fields but this should pull back your target.

SELECT A.NICKNAME, Z.REGION 
FROM ANIMAL A
LEFT JOIN MRec M ON M.NICKNAME = A.NICKNAME 
LEFT JOIN ZLoc Z ON Z.LOC_ID = M.LOC_ID 
WHERE Z.LOC_ID = 'North'

answered Nov 26, 2013 at 22:34

Smeghead Sev

4394 silver badges15 bronze badges

Comments

Theo Anderson · Accepted Answer · 2013-11-26 22:34:57Z

0

Try this:

SELECT nickname, region 
FROM Animal inner JOIN MRec
ON animal.animal_ID=MRec.Animal_ID
ON MRec.zloc_ID = zloc.zloc_ID
WHERE zloc.region = 'north'

answered Nov 26, 2013 at 22:34

Theo Anderson

1631 gold badge1 silver badge5 bronze badges

Comments

cooper667 · Accepted Answer · 2013-11-26 22:36:15Z

0

This works:

SELECT nickname, region 
FROM Animals 
JOIN MRec ON MRec.Animal_ID = Animals.id 
JOIN ZLoc ON MRec.Zloc = ZLoc.id 
WHERE ZLoc.region LIKE 'North'

Fiddle

answered Nov 26, 2013 at 22:36

cooper667

4254 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Dev Query with Joins

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related