php - MySQL need sub select or temporary table -

i have 2 tables.

first table - products products_id | quantity  1001,1 1002,2 1003,5 1004,4   second table - products_catalog products_catalog_id | products_id | partner_channel |active_status  1,1001,amazon,0 2,1001,ebay,0 3,1001,zalando,1 4,1002,amazon,1 5,1002,ebay,0 6,1003,amazon,1 7,1003,ebay,1 8,1003,zalando,1 9,1004,amazon,0 10,1004,ebay,0 11,1004,zalando,0 

i want have result of products id condition if product not active in partner channel (active_status = 0) using filter this:

 select p.products_id, pc.partner_channel, pc.active_status     products p       left  join products_catalog pc on  pc.products_id=p.products_id          pc.active_status='0'  group p.products_id order  p.products_id; 

where active_status = 0, result this:

products_id | partner_channel | active_status 1001,amazon,0 1002,ebay,0 1004,amazon,0 

i want have result table products this:

products_id | partner_channel | active_status 1004,amazon,0 

because in partner_channel product id (1004) have active_status = 0.

i think missed in filter, didn't have clue that. maybe should use sub query in when left join products_catalog?

any appreciated.

thanks.

you have place condition in having clause this:

select products_id products_catalog group products_id  having sum(active_status <> 0) = 0; 

this query returns products_id values having active_status = 0 all partner_channel related to.

you can use above query derived table join original tables in order rest of fields:

select p.*, pc.* products p join products_catalog pc on pc.products_id=p.products_id   join (     select products_id     products_catalog     group products_id      having sum(active_status <> 0) = 0 ) g on g.products_id = p.products_id order p.products_id 

demo here