mysql - Join three tables with JOIN and HABTM association -

i have 3 table items, characteristics, item_to_characteristics item_to_characteristics middle table keeps habtm association between 2 tables (items <-> characteristics).

item table has id|title|description

characteristic table has id|name|pic_path

item_to_characteristic has id|item_id|characteristic_id

so example: item id 25 want find characteristics.

table item_to_characteristics looks this:

so see item_to_characteristic hold id of item , characteristics.

this query have writen far doesent return want,

select item_characteristics.name, item_characteristics.icon_path  item_characteristics, item_to_characteristics itch inner join items on items.id=itch.item_id items.id=25; 

the query should return 5 rows.

you have cross joined table item_to_characteristics why getting more rows expecting. instead should inner join item_to_characteristics

select  ic.name, ic.icon_path     item_characteristics ic         inner join item_to_characteristics itch              on itch.characteristic_id = ic.id         inner join items             on items.id=itch.item_id   items.id = 25; 

this 1 of reasons advise use ansi 92 join syntax, not ansi 89 syntax have used above. aaron bertrand has made compelling case switch in this article.