SQL Nested Query with invalid relation error (ORA-00920 Error)

Question

I have 3 tables in total. I am trying to select the price of the cost food has to be the highest and the category name have to fall under organic.

I have came up with the following SQL QUERY but i have received an invalid relation error.

Could someone point me out what the problem might be ?

Thanks

SELECT PRICE, LABEL
FROM FOOD
WHERE FOOD.FOOD_ID = (
    SELECT FOOD_ID
    FROM FOOD_CATEGORY
    WHERE FOOD_CATEGORY.FOOD_ID = (
       SELECT FOOD_ID
       FROM CATEGORY
       WHERE NAME = 'ORGANIC'
    )
);

FOOD

FOOD_ID
LABEL
PRICE

FOOD_CATEGORY

FOOD_ID
CATEGORY_ID

CATEGORY

CATEGORY_ID
NAME

Relationship

FOOD.FOOD_ID = FOOD_CATEGORY.FOODID 

FOOD_CATEGORY.CATEGORYID = CATEGORY.CATEGORYID

Your subqueries are presumably returning more than one row, so you need in rather than =. But, you should really learn to write this query using explicit join syntax. — Gordon Linoff
– Gordon Linoff, Commented May 13, 2014 at 14:03

Rob van Laarhoven · Accepted Answer · 2014-05-13 14:10:08Z

4

I suspect you have more foods that are of category 'ORGANIC'. Otherwise there is no need for the table FOOD_CATEGORY. The table FOOD_CATEGORY is there to create a many-to-many relation.

So why not :

SELECT PRICE, LABEL
FROM FOOD
JOIN FOOD_CATEGORY ON (FOOD_CATEGORY.FOOD_ID = FOOD.FOOD_ID)
JOIN CATEGORY ON (CATEGORY.CATEGORY_ID = FOOD_CATEGORY.CATEGORY_ID)
WHERE CATEGORY.NAME = 'ORGANIC';

Or use IN instead of =

SELECT PRICE, LABEL
FROM FOOD
WHERE FOOD.FOOD_ID IN (
    SELECT FOOD_ID
    FROM FOOD_CATEGORY
    WHERE FOOD_CATEGORY.CATEGORY_ID = (
       SELECT CATEGORY_ID
       FROM CATEGORY
       WHERE NAME = 'ORGANIC'
    )
);

edited May 13, 2014 at 14:10

answered May 13, 2014 at 14:04

Rob van Laarhoven

8,9552 gold badges33 silver badges50 bronze badges

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

3 Comments

user2691544 Over a year ago

Thank you @Robert Merkwürdigeliebe How do i futher add MAX(Price) into the statement. Would a GROUP BY work ?

Rob van Laarhoven Over a year ago

GROUP BY is the way to go

Rob van Laarhoven Over a year ago

Try it. Takes less time then posting on SO ;-)

ashishmaurya · Accepted Answer · 2014-05-13 14:03:48Z

0

because its CATEGORY_ID instead of FOOD_ID

SELECT PRICE, LABEL
FROM FOOD
WHERE FOOD.FOOD_ID = (
    SELECT FOOD_ID
    FROM FOOD_CATEGORY
    WHERE FOOD_CATEGORY.FOOD_ID = (
       SELECT CATEGORY_ID
       FROM CATEGORY
       WHERE NAME = 'ORGANIC'
    )
);

answered May 13, 2014 at 14:03

ashishmaurya

1,19610 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL Nested Query with invalid relation error (ORA-00920 Error)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related