Using DISTINCT with FIND_IN_SET

Question

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

Tim Biegeleisen · Accepted Answer · 2016-10-07T05:28:20.000

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:

Using DISTINCT with FIND_IN_SET

1 Answers1

SQLFiddle