0

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Saad Bashir
  • 4,341
  • 8
  • 30
  • 60
  • Do you have spaces after the commas in the ptype field of the categories table? – Shadow Oct 06 '16 at 12:34
  • 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 Oct 06 '16 at 12:36

1 Answers1

1

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64