I have a MySQL database with a table 'items'.
Each item can have multiple prices in the 'prices' table and multiple images in the 'images' table. They both have a one to many relationship to items.
I'd love to have a query that gets me all the data at once, but in with the query I came up with, I get a lot of duplicate records. If an item has 3 prices and 3 images, I get 9 rows for that item (instead of the desired 6):
SELECT * FROM items LEFT JOIN prices ON items.item_id = prices.item_id LEFT JOIN images ON items.item_id = images.item_id ORDER BY items.item_id
E.g. I'd love to have:
itemid 1 item1 price1 NULL 1 item1 price2 NULL 1 item1 price3 NULL 1 item1 NULL image1 1 item1 NULL image2 1 item1 NULL image3 2 item2 ....