CODEWITHSUNDEEP
sql
Sheldonbrax
Sheldonbrax

Reputation: 320

SQL table join where single id has multiple results

I am using a SQL query to display products from my database with a single preview image for each. For this I'm using a table join to combine the images table with the products.

The problem I am having is that multiple images are applied to each product id and as such, I am getting duplicates. I simply need to find a workaround that will allow me to get a single image for each product id, without limiting the products themselves.

Here is my code:

SELECT prod.id, prod.name, prod.description, img.img
FROM product AS prod
LEFT JOIN img 
ON prod.id = img.product_id;

For the most part this works, except for the duplicates that are created for each product.

I feel I should also mention that I am displaying the results using php with a PDO command

Upvotes: 0

Views: 203

Answers (1)

jarlh
jarlh

Reputation: 44766

Either do a group by:

SELECT prod.id, prod.name, prod.description, MAX(img.img)
FROM product AS prod
LEFT JOIN img
ON prod.id = img.product_id
GROUP BY prod.id, prod.name, prod.description

Or do a correlated sub-select in the select list:

SELECT prod.id, prod.name, prod.description,
       (SELECT MAX(img.img) FROM img WHERE prod.id = img.product_id)
FROM product AS prod

Upvotes: 1

Related Questions