MySQL Query returns duplicates

Question

I am very new to SQL

when I execute the below query it returns the following results:

SELECT table1.image_name, table1.item_id
FROM table1, table2, table3
WHERE table2.catagory="item"
AND table3.account_id=59

image_name    image_id
-------------------------
d9.jpg      89
d9.jpg      89
d9.jpg      89
d9.jpg      89
d10.jpg      90
d10.jpg      90
d10.jpg      90
d10.jpg      90
etc.....

The results repeat the same attribute four times! I don't understand why this is and I cannot find a reason for this anywhere!

I have 3 tables, table1 PK(image_id) is referenced as the FK in Item. Item PK(Item_id) is referenced with FK in table3. table3 has a compound key from table 4(account_id) and table2(item_id).

My query is clearly wrong, but I don't understand why? I hope someone could provide an explanation to the result!, or help point me in the right direction!

Thanks

Answer 1

you are listing 3 tables, and only joining on two of them. that means you will get a row back from the join, and it will repeat once for every row in the third table (table1 in your case)

Answer 2

you have to state the join conditions explicitly - otherwise you get cartesian product of all 3 tables for all items 59

where table1.image_id = table2.item_id
and table2.item_id = table3.item_id
and ...