MySQL large join query with multiple values where values help

Question

I have the following query that selects most of the information I need:

SELECT cm.*, companies.company_name, companies.permalink,last_met.*,(cm.total_unique_visitors - last_met.last_UV)/last_met.last_UV * 100 as percent_change FROM calculated_metrics as cm
    LEFT JOIN companies ON companies.company_id = cm.company_id
    LEFT JOIN (SELECT company_id,total_unique_visitors as last_UV FROM calculated_metrics WHERE MONTH(date) = '04' AND YEAR(date) = '2011' GROUP BY company_id) last_met ON cm.company_id = last_met.company_id                         
    WHERE MONTH(cm.date) = '05' AND YEAR(cm.date) = '2011'
    AND cm.total_unique_visitors >3000
    AND cm.total_unique_visitors<9999999
    ORDER BY percent_change DESC

I have now been asked to sort the data even more. I need to sort the data by 2 tables(marketing_strategy and product_type). These tables both contain a company_id and multiple records for each company Example

marketing_strategy (table)
company_id marketing_strategy_option_id
605          25
605          9
604          21
604          9

product_type (table)
company_id product_type_option_id
605          12
605          13
604          13
604          3

What is the best way to sort using these 2 tables and the query above?
I will have a comma delimited string for the marketing_strategy options and the product_type options. (Ex. marketing_strategy: 25,9 product_type: 13,3)

Would it be easier to run the query above loop through using PHP and then select only the companies that contain the specific marketing_strategy and product_type id's?

I am at a loss here.

score 0 · Answer 1 · edited May 23 '17 at 11:48

I think I might have answered my own question. Reading through SO I found what I was looking for: Multiple Where conditions on same column
So far query is working as expected.

SELECT cm.*, companies.company_name, companies.permalink,last_met.*,company_type_options_id,company_marketing_options_id,(cm.total_unique_visitors - last_met.last_UV)/last_met.last_UV * 100 as percent_change FROM calculated_metrics as cm
LEFT JOIN companies ON companies.company_id = cm.company_id
LEFT JOIN (SELECT company_id,total_unique_visitors as last_UV FROM calculated_metrics WHERE MONTH(date) = '4' AND YEAR(date) = '2011' GROUP BY company_id) last_met ON cm.company_id = last_met.company_id
LEFT JOIN company_marketing_strategy ON cm.company_id = company_marketing_strategy.company_id
LEFT JOIN company_type ON cm.company_id = company_type.company_id
WHERE MONTH(cm.date) = '05' AND YEAR(cm.date) = '2011'
AND company_marketing_options_id IN (25,21)
AND company_type_options_id IN (3)
AND cm.total_unique_visitors >3000
AND cm.total_unique_visitors<9999999999
GROUP BY cm.company_id
HAVING COUNT(DISTINCT company_marketing_options_id) = 2
AND COUNT(DISTINCT company_type_options_id) = 1
ORDER BY percent_change DESC

MySQL large join query with multiple values where values help

1 Answers1