mysql - Joining Two Tables In One Join -

i need filter query result 1 of selected aggregated fields meets conditions. in case, have nvk.quality_id being equal 81 or 82. 81 or 81 condition applies 1 field counting not other field. query work expected:

select      cams.name campaign_name,     clk.type,     count(distinct clk.eid) dist_eids_sent,     count(distinct nvk.eid) dist_leads 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 , quality_id in (81,82) # hot, warm group campaign_name, type 

the results here this:

campaign name | type | dist_eids_sent | dist_leads dogs          | 1    | 1000           | 100 cats          | 1    | 900            | 80 

these results correct , in line our expectations. 81 , 82 descriptions of type of dist_leads. want hot or warm ones in result.

but, rather and quality_id in (81,82) # hot, warm use english meaning of these 2 ids in selector.

to must join query bm_config.classes cls on nvk.quality_id = cls.id , (cls.cid = 156 , cls.name in ('hot', 'warm'))

this looks like:

select      cams.name campaign_name,     clk.type,     count(distinct clk.eid) dist_eids_sent,     count(distinct nvk.eid) dist_leads 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 , cls.name in ('hot', 'warm')) group campaign_name, type 

but results this:

campaign name | type | dist_eids_sent | dist_leads dogs          | 1    | 1000           | 990 cats          | 1    | 900            | 850 

whereas before dist_leads ~ 10% of dist_eids_sent 90%+

i don't understand happening here. looks additional join joining onto base table clk , altering result

is possible edit left join onto nvk join records correspond cls.name being "hot" or "warm". "nested join" if will.

it this:

 left join bm_emails.nvk156 nvk on nvk.eid = clk.eid ,     bm_config.classes cls on nvk.quality_id = cls.id ,     (cls.cid = 156 , cls.name in ('hot', 'warm') 

hope i'm asking makes sense. i'm trying add filtering 1 left join not effect rest of query results.

forgive me if not quite right. don't use mysql!

select      cams.name campaign_name,     clk.type,     count(distinct clk.eid) dist_eids_sent,     count(distinct nvk.eid) dist_leads bm_arc.clicks156 clk inner join bm_queue.campaigns cams on clk.camp = cams.id      left join      ( select          eid           bm_emails.nvk156          inner join bm_config.classes cls on nvk.quality_id = cls.id          cls.name in ('hot', 'warm')     )  nvk on nvk.eid = clk.eid group campaign_name, type