CODEWITHSUNDEEP
mysqlsql
rx1984
rx1984

Reputation: 1222

OUTER JOIN Syntax error in SQL statement "

Could anyone help me understand why this query is not working?

Modify the query to list all the products (product) and their associated category (product_category) if they have one. Otherwise, do not display a value (null) for the category.

-- SQL request(s)​​​​​​‌​​‌​​​‌​‌‌‌‌​‌​​‌​​​​​‌‌ below
SELECT P.name as PRODUCT_NAME, PC.name as CATEGORY_NAME
FROM product as P
OUTER JOIN PRODUCT_CATEGORY as PC on P.product_id =  PC.product_category_id

it keeps showing me this error:

Syntax error in SQL statement "                       
SELECT P.NAME AS PRODUCT_NAME, PC.NAME AS CATEGORY_NAME
FROM PRODUCT AS P
OUTER[*] JOIN PRODUCT_CATEGORY AS PC ON P.PRODUCT_ID =  PC.PRODUCT_CATEGORY_ID
"; SQL statement:
-- SQL request(s) below
SELECT P.name as PRODUCT_NAME, PC.name as CATEGORY_NAME
FROM product as P
OUTER JOIN PRODUCT_CATEGORY as PC on P.product_id =  PC.product_category_id
 [42000-200]

enter image description here

Thanks a lot

Upvotes: 1

Views: 1035

Answers (3)

Israa
Israa

Reputation: 1

as long as category null values are not allowed, then I think you should use inner join

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562280

OUTER JOIN is not valid syntax in ANSI SQL or any implementation I've used.

Here's a good reference: https://crate.io/docs/sql-99/en/latest/chapters/30.html#joined-tables

You can say JOIN or INNER JOIN. These are synonyms.

You can say LEFT JOIN or LEFT OUTER JOIN. These are synonyms.

You can say RIGHT JOIN or RIGHT OUTER JOIN. These are synonyms.

You can say FULL JOIN or FULL OUTER JOIN. These are synonyms.

You should see the pattern now. The words INNER and OUTER are optional keywords.

OUTER JOIN (without LEFT, RIGHT, or FULL) is not one of the choices.

I suggest to satisfy your recruiter, you use LEFT OUTER JOIN.

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

Hmm, I don't know if OUTER JOIN is synonymous with FULL [OUTER] JOIN in any DBMS... So OUTER JOIN should probably read FULL OUTER JOIN or simply FULL JOIN.

But regardless, you don't need a full join here. A full join would list categories without a product. You need a a left join.

SELECT p.name AS product_name,
       pc.name AS category_name
       FROM product AS p
            LEFT JOIN product_category AS pc
                      ON p.product_id = pc.product_category_id;

Upvotes: 1

Related Questions