Ali Ali - 27 days ago 6
SQL Question

how to call subquery inside WHEN Clause - Mysql

i have two tables :

Product :

id name category
1 AAA BBB
2 CCC DDD
3 EEE FFF


Ordre:

id id_product date
1 2 10/11/16
2 2 06/16/16
3 3 12/09/16
4 1 02/06/16
5 3 15/10/16


in order to know if a product has an order i create this select query :

SELECT id,name ,category

CASE WHEN id IN (select id_product from Ordre) then 'Y'
ELSE 'N' END AS has_ordre

FROM product;


but this is not working for me, in the fact i want to create a View that contains the info about the products and also has the column "has_ordre" to check if a product has an ordre or not.

do you have any suggestion?
i'm new in Mysql
thanks in advance.

Answer

You can do what you need without a sub-query by doing a join on the two tables and a COUNT() on order.id_product, which will count the number of orders for each product. NOTE: this query will completely (as designed) exclude products which have no orders. Also, the result for this particular query will show the number of orders for each product:

SELECT p.id, p.name, p.category, COUNT(o.id_product) AS num_orders
FROM product p
    RIGHT JOIN order o
        ON p.id = o.id_product
GROUP BY p.id
ORDER BY num_orders

You can change the RIGHT JOIN to a LEFT JOIN if you would like to display all orders regardless of whether or not they have an active order:

Here is a fiddle

Comments