I did the exercise solution as a subquery, but unfortunately, got very different results from the presented JOIN method.
After examining the table contents in order_items, it appears that the quantity of the product order for each product_id exceeds the presented exercise results. In other words, and perhaps I am confused, but it appears that the quantities ordered for each product_id total higher numbers, and there is a NULL value for a product that was never ordered. Mosh has pork bacon as two orders and I have orders for the product totaling seven.
So, the exercise to demonstrate the IF conditional is lacking, because the NULL order item results in a ‘Once’ order, while the remainder are ‘Many times’. So, I want to use my subquery to not include the NULL value, but that may require more complex code, so I may need to revert to a JOIN with LEFT JOIN.
Any ideas?
My code below:
USE sql_store;
SELECT
product_id,
name,
(SELECT SUM(quantity)
FROM order_items WHERE product_id = p.product_id) AS orders,
(SELECT IF(orders>1, ‘Many times’, ‘Once’)) AS frequency
FROM products p;