I have the following tables: shops and orders.
I need to select all shops assigned to a specific user, but only get the orders between a specific date range, im trying to do this in one query but cant seem to get it correct.
For example, I want all of Alans assigned shops, so I will do something like the following:
SELECT s.shop_id, s.shop_name, u.first_name as agent_name FROM shops s JOIN users u ON u.id = s.user_id WHERE s.user_id = 123;
I then need all that shops orders, so I can then join on the orders table like so:
SELECT s.shop_id, s.shop_name, u.first_name as agent_name, count(o.shop_id) FROM shops s JOIN users u ON u.id = s.user_id JOIN orders o ON o.shop_id = s.id WHERE s.user_id = 123;
However, my predicament is that i only want to bring back the orders within a partular time frame, bearing in mind that some shops may not have orders in the timeframe so selecting from the orders table and joining the shop will not always be correct.
Any help is greatly appreciated!
You need to sign in to view this answers
