CODEWITHSUNDEEP
mysqlinner-join
onegun
onegun

Reputation: 803

Mysql inner join queries usage

These are two tables: one category table and another product table

category table

id     name
1      mealA
2     mealB

product table

id     name     category_id ( refer to category table)
1     chicken     1,2
2     fries      1
3     tomato     2

SELECT * FROM product INNER JOIN category on product.category_id = category.id where category.name='mealA'

i am a bit shocked to find that the chicken is displayed as the result(by other ppl's code). It is my first time i know i can inner join comma seperated column directly with other column.

I did research online, seem there is another answer by using find_in_set in mysql, why inner join is able to do this, can anyone help to explain?

Upvotes: 1

Views: 40

Answers (2)

Barmar
Barmar

Reputation: 781096

When you compare an integer column (category.id) with a string column (product.category_id), the string value is converted to an integer before performing the comparison. When converting a string to an integer, it ignores everything after the initial numeric part, so the string 1,2 is converted to the number 1.

So chicken will match mealA, but it won't match mealB because 2 is after the ,.

Upvotes: 1

Arth
Arth

Reputation: 13110

I don't think this is working as you think it is; it shouldn't.

I'm guessing that product.category_id = category.id is converting product.category_id to an INT for comparison, thus taking the 1 from '1,2' which matches the 1 from the category table.

I much prefer a properly defined HAS_MANY relation (one row per category relation) to a CSV field, especially for reference. Anything else opens up a world of complexity.

Upvotes: 2

Related Questions