CODEWITHSUNDEEP
mysqljoin
Doug Fir
Doug Fir

Reputation: 21204

Joining Two Tables In One Join

I need to filter a query result so that one of the selected aggregated fields meets certain conditions. In this case, have nvk.quality_id being equal to 81 or 82. The 81 or 81 condition only applies to one field I am counting not the other field. This query DOES work as expected:

SELECT 
    cams.name AS Campaign_Name,
    clk.type,
    COUNT(DISTINCT clk.eid) AS Dist_Eids_Sent,
    COUNT(DISTINCT nvk.eid) AS Dist_Leads
FROM bm_arc.clicks156 clk
INNER JOIN bm_queue.campaigns cams ON clk.camp = cams.id 
LEFT JOIN bm_emails.nvk156 nvk ON nvk.eid = clk.eid AND quality_id IN (81,82) # Hot, Warm
GROUP BY Campaign_Name, type

The results here look something like this:

Campaign Name | type | Dist_Eids_Sent | Dist_Leads
dogs          | 1    | 1000           | 100
cats          | 1    | 900            | 80

These results are correct and in line with our expectations. 81 and 82 are descriptions of the type of Dist_Leads. We want Hot or Warm ones in the result.

But, rather than say AND quality_id IN (81,82) # Hot, Warm I would like to use the English meaning of these two IDs in the selector.

To do that I must join the query to bm_config.classes cls ON nvk.quality_id = cls.id AND (cls.cid = 156 AND cls.name IN ('Hot', 'Warm'))

This looks like:

SELECT 
    cams.name AS Campaign_Name,
    clk.type,
    COUNT(DISTINCT clk.eid) AS Dist_Eids_Sent,
    COUNT(DISTINCT nvk.eid) AS Dist_Leads
FROM bm_arc.clicks156 clk
INNER JOIN bm_queue.campaigns cams ON clk.camp = cams.id 
LEFT JOIN bm_emails.nvk156 nvk ON nvk.eid = clk.eid
LEFT JOIN bm_config.classes cls ON nvk.quality_id = cls.id AND cls.name IN ('Hot', 'Warm'))
GROUP BY Campaign_Name, type

But then the results look something like this:

Campaign Name | type | Dist_Eids_Sent | Dist_Leads
dogs          | 1    | 1000           | 990
cats          | 1    | 900            | 850

Whereas before Dist_Leads was ~ 10% of Dist_Eids_Sent it is now about 90%+

I don't understand what is happening here. It looks like the additional join is joining onto the base table clk and altering the result

Is it possible to edit my LEFT JOIN onto nvk so that this join is only for records that correspond to cls.name being "hot" or "Warm". A "nested join" if you will.

It would look something like this:

 LEFT JOIN bm_emails.nvk156 nvk ON nvk.eid = clk.eid AND
    bm_config.classes cls ON nvk.quality_id = cls.id AND
    (cls.cid = 156 AND cls.name IN ('Hot', 'Warm')

Hope what I'm asking for makes sense. I'm basically trying to add all filtering to one left join so as not to effect the rest of the query results.

Upvotes: 1

Views: 55

Answers (1)

Lukos
Lukos

Reputation: 1852

Forgive me if that is not quite right. I don't normally use MySql!

SELECT 
    cams.name AS Campaign_Name,
    clk.type,
    COUNT(DISTINCT clk.eid) AS Dist_Eids_Sent,
    COUNT(DISTINCT nvk.eid) AS Dist_Leads
FROM bm_arc.clicks156 clk
INNER JOIN bm_queue.campaigns cams ON clk.camp = cams.id     
LEFT JOIN 
    ( SELECT
         eid 
         FROM bm_emails.nvk156
         INNER JOIN bm_config.classes cls ON nvk.quality_id = cls.id
         WHERE cls.name IN ('Hot', 'Warm')
    )  nvk ON nvk.eid = clk.eid
GROUP BY Campaign_Name, type

Upvotes: 1

Related Questions