MySQL Querying Multiple Tables

Question

Thank you for taking a look at my question! I've been trying to figure out a single query to do the following but have been unsuccessful. I would truly appreciate any help. Thank you in advance :-)

I am making an admin page for my e-commerce store that shows products that haven't sold in the last X days. There are three tables that need to be used...

Table: products
Column: product_id (int) 

This table/column contains all the products in the store

 Table: orders
 Columns: order_id (int), date_ordered (datetime)

This table contains all the orders which are identified by order_id and the date for which they were ordered (date_ordered).

 Table: order_products
 Column: order_id (int), product_id (int) 

This table contains a complete listing of all products ordered (product_id) and the corresponding order (order_id).

So, the query I'm trying to figure out would use use the order_id in tables orders and order_products to determine which products have sold in the last X days... Then return any products_id from the products table which have not sold in the last X days.

Any suggestions? Any help would be very appreciated! Thank you :-)

Answer 1

Okay so while I agree in part that you should do some poking around and learn more about left joins, there is also some trickiness to answering this question correctly that might be lost on a beginner. I'm gonna go ahead and help you answer it, but I would recommend learning more about joins.

My exact query would depend on the available indices, but it very likely resemble something like this:

SELECT a.* 
FROM products AS a
LEFT JOIN (
SELECT product_id FROM order_products as b 
INNER JOIN orders AS c
ON b.order_id = c.order_id
WHERE c.date_ordered >= date_sub(c.date_ordered, INTERVAL 7 day)
GROUP BY product_id 
) AS d
ON a.product_id = d.product_id 
WHERE d.product_id IS NULL

What I'm doing is I'm writing a subquery that joins orders and orders products together, where date_ordered falls within a certain date range (I would recommend learning about the date_sub function here: http://www.w3schools.com/sql/func_date_sub.asp and also do a few quick SELECT date_sub(date_ordered, INTERVAL X DAY) FROM orders queries to make sure you understand how this calculation works, in practice.

Now, I get my list of orders for the last X days (7 in the query above) and I join it with the orders product table to get the products that were ordered. Here, I want to dedup my products, basically. Product_id = 300 may have been ordered 70 times. Product_id = 200 may have been ordered 50 times. Whatever the case may be, I don't want to join 70 records and 50 records to my product table for product ids 300 and 200, so I dedup them. That last GROUP BY statement does that. It's functionally the same thing as writing DISTINCT (although there can be minor differences in how these are computed in certain circumstances, none of those circumstances seem to apply here... use DISTINCT if that's clearer for you)

Once I have my list of unique product ids that were ordered in the past X days, I join that with my product table. Here, I use a left join. Like the comments noted above, you'll want to look into the notion of joins pretty carefully. Do that, if you haven't already.

Last, I apply a WHERE filter that says "WHERE d.product_id IS NULL." What this is doing is saying, "okay, if product_id = Y was ordered in the past X days, then it will join to my products table successfully with a.product_id = d.product_id. If it wasn't ordered, then a.product_id will exist in my result set, but d.product_id won't. That is, d.product_id will be null."

That last twist may be the part that's not apparent / standing out.

Hope this helps.