Search code examples
mysqlselectfind-in-set

MySQL - Using FIND_IN_SET on Multiple Tables with No Relation

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.

Solution

  • 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.