Saad Bashir Saad Bashir - 1 month ago 18
MySQL Question

Using DISTINCT with FIND_IN_SET

I want to select DISTINCT(p.ptype) at the same time I also want to get the c.category if p.ptype is not in the set of c.ptype

Database Table: p

id ptype
1 Shirts
2 Cups
3 Shirts
4 Mugs


Database Table: c

id category ptype
1 Test Pants, Shirts, TShirts
2 Test1 Cups, Mats, Rugs


The SQL command I tried is as follows

SELECT DISTINCT(p.ptype), IF(FIND_IN_SET(p.ptype, c.ptype), c.category,'') as category
FROM p, c


This outputs p.ptype which are in set twice. One time with a blank c.category field and one with filled c.category.

However the desired output is as follows

ptype category
Shirts Test
Cups Test1
Mugs

Answer

Try doing an explicit LEFT JOIN on the ptype from the p table being present in the CSV list in the c table:

SELECT DISTINCT p.ptype, COALESCE(c.category, '') AS category
FROM p
LEFT JOIN c
    ON FIND_IN_SET(p.ptype, c.ptype) > 0

In your original query, were doing a cross join. This generates all possible combinations between the records of the two tables. It would be difficult to arrive the correct answer using a cross join, so a left join is preferable.

Demo here:

SQLFiddle

Comments