MySQL - Using FIND_IN_SET on Multiple Tables with No Relation

Question

My database structure looks something as follows: Database Name: Products

id ptype
1  Cups
2  Shirts
3  Cups
4  Mugs
5  Fabric
6  Mat

Database Name: Categories

id category ptype
1  Clothes  Pants, Shirts, Tshirts
2  Other    Mugs, Cups

I want to get a list of distinct ptype (products table) which aren't already listed in ptype of categories table

So the result would be

ptype
Fabric
Mat

I tried using the following mysql select query but it doesn't seem to work

SELECT p.ptype, c.ptype, FIND_IN_SET(p.ptype,c.ptype) FROM products as p, categories as c WHERE FIND_IN_SET(p.ptype,c.ptype) < 1

It returns value of FIND_IN_SET as 0 for all the ptypes of products table.

Do you have spaces after the commas in the ptype field of the categories table? — Shadow
Ahh Thank you for pointing out yes I do have spaces after the commas which shouldn't be there. And removing that resolves the problem too. Post this in the answers and I shall accept it as the solution to the question. Thanks again — Saad Bashir

Shadow Shadow · Accepted Answer · 2016-10-06T12:42:27

Remove the spaces after the commas in categories.ptype field to make the query work. find_in_set() compares the needle to a list of comma separated values and considers the space after the commas to be part of the strings it searches, therefore no match is found if you search for a string that does not contain the spaces.

MySQL - Using FIND_IN_SET on Multiple Tables with No Relation

1 Answers