Search code examples
mysqlsqlstringinner-joinaggregate-functions

Mysql join & filter

How to Add another table as a condition in below query

i have below query

SELECT Group_concat(cl.name) AS category  FROM sco_category_product cp 
LEFT JOIN sco_category_lang cl ON (cp.id_category= cl.id_category) WHERE id_product = 3 AND cl.id_shop = 1

Gives me output

category
Juniors,Juniors size chart

I have a table "sco_category" that has "id_category" & "active" columns in it.

I want to make a query that merges with above query I've written & give me output of active "id_category" only.

For exg "Juniors" is name of "id_category" & if is not active i.e ("active" column has 0 value in it.) then its excluded from data.

Please share your thought on this.

Solution

  • If I follow you correctly, that's one more join:

    select group_concat(cl.name) as category  
from sco_category_product cp 
inner join sco_category_lang cl on cp.id_category= cl.id_category 
inner join sco_category c on c.id_category = cl.id_category
where cp.id_product = 3 and cl.id_shop = 1 and c.active = 1