I am trying to get a query to return all the relevant records for products which feature in my featured projects table. All is working for the simple MYSQL query however I want to get the Thumb Image for each product two which is stored in another table (product_image) but with a catch. In this "product_image" table not every products (product_id) will have a record as it is only populated on upload of images and some products may have several records (images stored) I only want one record returned for each product ID. So a product record in the products table has a 0 to many relationship with the product_image table.
I have a working basic SQL statement for the basics without returning the image, I can code a query that only returns the products who have records in the product image table but I need the query to return all the rows in the outer query and then 1 thumb_img that matches the product id from the image table and if there is no images it can return a Null value.
Is this possible? Below is my latest attempt but this just returns the matching thumb for the first product_ID and duplicates this for the other 3(in this case due to limit at end) records. I need the matching thumb for each record rather then the matching one for the first record repeated between the rest. And if there is no records for that product ID a null returned.
SELECT * FROM `products`, `featured_products`, `shop`, (SELECT thumbSrc
FROM product_image
WHERE products.productId = product_image.productID
ORDER BY product_image.position ASC
LIMIT 1) image
where shop.shopId = products.shopId AND featured_products.productId = products.productId AND visible = '1' LIMIT 4
Thanks in advance for any help / feedback. Is it possible to do this as I want to keep my database as normalized as possible rather then result in storing a thumb field in the products table too.