How to work out the correct logic for a MySQL query

Question

I have a module that generates sales report for me and it has multiple options for filtering the data. The basic query for this is

SELECT DISTINCT o.orders_id from orders o 
WHERE o.date_purchased >= '2017-01-01 00:00:00'
AND o.date_purchased < '2017-04-28 00:00:00';

If I wanted to only search for sales within the UK it would add

AND FIND_IN_SET (o.customers_country, 'United Kingdom')

I want to have the ability to filter by custom id so that I can generate a report that only looks at specific products. This id is stored in

products_with_attributes_stock.customid

I've included a list of the tables that contain the data needed to cross reference to collect the customid. See bellow:

I could do several queries and use the result from one to filter the results of the next until I get the customid, but that seems rather clunky.

I think it can be done using INNER JOIN, but to be honest I'm rather confused as to how to join all the tables and matching columns together.

Any help to understand how to achieve this would be appreciated as I need to do more of the same for other filtering options.

Yes you want to use joins. .I don't see it as good question, you basically need to learn how joins work, you should read manual or tutorial — Peter, Apr 27 '17 at 15:22
Possible duplicate of [How can I join multiple SQL tables using the IDs?](http://stackoverflow.com/questions/9853586/how-can-i-join-multiple-sql-tables-using-the-ids) — Juan Carlos Oropeza, Apr 27 '17 at 15:27
Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Apr 27 '17 at 15:28

score 0 · Answer 1 · answered Apr 27 '17 at 20:37

I resolved this issue.

Final query I wrote was:

SELECT DISTINCT o.orders_id from orders o
LEFT JOIN orders_products op ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa ON o.orders_id = opa.orders_id
AND op.orders_products_id = opa.orders_products_id
LEFT JOIN products_attributes pa ON opa.products_options_id = pa.options_id
AND opa.products_options_values_id = pa.options_values_id
LEFT JOIN products_with_attributes_stock pas ON op.products_id = pas.products_id
AND pa.products_attributes_id = pas.stock_attributes
WHERE customid = '00038-16-00008'
AND o.date_purchased >= '2017-04-25 00:00:00' AND o.date_purchased < '2017-04-26 00:00:00' 
ORDER BY o.orders_id DESC

How to work out the correct logic for a MySQL query

1 Answers1