CODEWITHSUNDEEP
mysqlsqlselectcase
user3358413
user3358413

Reputation: 13

MySQL SELECT statement inside CASE

I have a PRODUCTS table which contains product records or subproducts records. Subproducts are indicated by having an entry in the HIGHERCATALOGID column, while for products the HIGHERCATALOGID column is NULL. I'm trying to write a query that would output product name if catalogid is pointing to a product record, or name of the parent product if catalogid is pointing to a subproduct. Here is what I tried to do:

SELECT p.catalogid, p.highercatalogid, oi.orderid
CASE
    WHEN highercatalogid is null then cname\
ELSE
    SELECT cname from products p1 where p.highercatalogid=p1.catalogid
END as name
FROM products p, oitems oi
WHERE p.catalogid=oi.catalogid

However, this results in an error.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case WHEN highercatalogid is null then cname else select cname from products p' at line 2

What is the correct way of doing this?

Upvotes: 1

Views: 6080

Answers (2)

peterm
peterm

Reputation: 92785

You can try it this way

SELECT p.catalogid, p.highercatalogid, oi.orderid,
       COALESCE(p2.cname, p.cname) cname
  FROM oitems oi JOIN products p
    ON oi.catalogid = p.catalogid LEFT JOIN products p2
    ON p.highercatalogid = p2.catalogid

Here is SQLFiddle demo

Upvotes: 2

realnumber3012
realnumber3012

Reputation: 1062

SELECT p.catalogid, p.highercatalogid, oi.orderid

,

CASE
WHEN highercatalogid is null then cname
ELSE
SELECT cname from products p1 where p.highercatalogid=p1.catalogid
END as name
FROM products p, oitems oi 
WHERE p.catalogid=oi.catalogid

Upvotes: 1

Related Questions