Unanswered: Selecting Individual Rows From Joined Table
I am trying to select one individual record from multiple tables.
I have a products, product_images, category, sub-category and main table.
The product table holds product name, the product_images table holds images relating to the product id, the category and sub-category tables hold the category info and the main table holds them all together.
I wish to select just one image from the images table along with the one product.
What I have so far is
SELECT DISTINCT products.product_name, product_images.thumbnail, product_images.main_image, product_options.price, products.product_id, category.cat_name, sub_category.sub_category_name
FROM product_images, main INNER JOIN product_options ON main.product_options_id = product_options.product_options_id INNER JOIN products ON main.product_id = products.product_id INNER JOIN category ON main.category_id = category.category_id INNER JOIN sub_category ON main.sub_category_id = sub_category.sub_category_id;
The trouble is it is displaying all of the images for each product-id,how do I get it to just display the one image per product-id?
FROM main as M
JOIN product_options as PO
ON PO.product_options_id = M.product_options_id
JOIN products as P
ON P.product_id = M.product_id
JOIN category as C
ON C.category_id = M.category_id
JOIN sub_category as SC
ON SC.sub_category_id = M.sub_category_id
JOIN product_images as PI
ON PI.product_id = M.product_id
AND PI.thumbnail =
( select max(thumbnail)
where product_id = M.product_id )
FROM products p
INNER JOIN product_images p_i
ON p.product_id = p_i.product_id
WHERE p_i.thumbnail IN
WHERE product_images.product_id = p.product_id
Say I have a date field and I use the same structured query using MAX(MyDate) and my table has more than one record with the same MyDate - what will it do? Will it pick up the record that is "physically" maximum?