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

Solution

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

SQLFiddle