join assist

[lifeworks]

Hi

Im rubbish MySQL joins, and need an assist please.

i have a product table and an images table. Each product has 4 images associated to it by a foreign key product id in the images table.

I need to pull out the product info (from prod table) and 1 image, can anyone help? I can it it with multiple queries, but was hoping for a cure all solution...

Thanks
lworks

[pritaeas]

select * from products,images where products.id = images.product_id

[lifeworks]

Thanks for the input,

Unfortunately this is similar to my best effort so far, it pulls out 4 of the same product, one for each image... I need to limit it to one instance of the product,

lworks

[ferensick]

Hi lifeworks, this could work, but does it matter which image is pulled for each product?

SELECT * FROM products, images WHERE products.id = images.product_id ORDER BY ____ LIMIT 0, 1

Depending what your ORDER BY column(s) are, will determine which image is selected first... this could help if you need a specific image for a given product id.
I just realized this of course will work for 1 product but obviously not for multiple products. I'm think a subquery may work. Let me sit on it for a couple minutes. I'll post back.

[ferensick]

Hi lifeworks. There could be an easy way to do this, depending on the values in your table. I'll explain first then write a sample query. All you need to do is select your records from the IMAGES table and ORDER them in such a way that the product ID is ordered in a non-repetitive way. Example; Product_ID in IMAGES table should be ordered like this: 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9... (assuming there are only 9 product_id's in the IMAGES table).
This way you can do this:

SELECT * FROM products p JOIN
(SELECT * FROM images ORDER BY _______) i ON p.id = i.product_id LIMIT 0, 9

Let me know if this is clear enough or if it's possible. Also give some more information if you need to. =) I hope this can help.
Do a LEFT JOIN if you want all the records in the products table regardless if it has an image associated with it or not.

[ferensick]

Sorry, 1 more suggestion:
You can also use the multiple queries you have and put it in 1 SQL statement, like this:
SELECT
(SQL Query 1) a,
(SQL Query 2) b,
(SQL Query 3) c,
(SQL Query 4) d
FROM dual --this line is optional... will work without 'FROM dual'

The advantage of using subqueries this way is that you can turn this into a VIEW. You can not use a SUBQUERY in the FROM clause in a view. The first solution I posted above can not be turned into a view with MySQL 5x. (Not sure about MySQL 6x). However when using subqueries in this way... you can use a VIEW:

CREATE OR REPLACE VIEW v_products_images AS
SELECT
(SQL Query 1) a,
(SQL Query 2) b,
(SQL Query 3) c,
(SQL Query 4) d

[lifeworks]

Thanks very much ferensick, its night time here, currently dont have access to try it out, but Ill give you some more info, maybe that will help...

OK, so the product table has id and other columns like name, timeadded etc, nothing important.

The images table has an id, a product_id and 4 different image paths for 4 versions (diff dimensions) of the same photo... this is what happens when graphic designers have their way :)

I need to pull out all products, with a photo for each - doesn't matter much which one... and I'd need to get every product, regardless of whether a photo is attached (there should always be a photo anyway)...

Which solution do you recommend - Im a bit wary of your last one, as Im unfamiliar with views hehe, havent sunk my teeth into databases yet, still hammering the PHP and Javascript :)

Thanks again for taking the time to lend a hand!

[pritaeas]

SELECT * FROM products,images WHERE products.id = images.product_id GROUP BY products.id

[lifeworks]

Victory!!

That nailed it! Much appreciated - I dont fully understand why, Im assuming that ORDER BY suffix creates a limit, thanks very much to everyone who contributed!

lworks