Do Not Repeat (DISTINCT) One Column On A Table With MySQL

Question

When I use this:

SELECT DISTINCT id FROM table

this works! but... when I want to filter only one column I try to do this:

SELECT DISTINCT prod_id, id, prod_picture FROM products

this gives me all table... I just need 1 picture for each product, like:

1 | 1 | asd.jpg
2 | 3 | weq.jph

not

1 | 1 | asd.jpg
1 | 2 | qwe.jpg
2 | 3 | weq.jpg

actually I try to use this:

SELECT DISTINCT 
  prod_list.id,
  prod_list.prodname,
  prod_pict.pict_file
FROM
  prod_list
  INNER JOIN urun_pict ON (prod_list.id = prod_pict_prod_id)

I have to filter just "prod_list.id"...

Answer 1

You should GROUP BY the product id to collapse all rows for each id into one. All columns which are not part of your GROUP BY clause should be aggregate columns. You need to tell MySQL which of the possibly multiple values for the other columns you want. If you don't care, use MIN or MAX?

SELECT 
  prod_list.id, 
  prod_list.prodname,
  MAX(prod_pict.pict_file) AS `pict_file`,
FROM 
  prod_list
INNER JOIN
  prod_pict
ON
  prod_list.id = prod_pict.prod_id
GROUP BY
  prod_list.id,
  prod_list.prodname

Answer 2

SELECT prod_id, id, prod_picture
  FROM products
  GROUP BY prod_id