List only one image of a product

Question

I am doing a project, where will allow you to register more than one image per product, this has been generating me a certain difficulty. I wanted to list the products that have image and only an image that same product.

Obviously there may be an easier way to do that

I’ve tried some ways that haven’t been giving the right result that I hope.

The closest to the final result was the one below, but duplicates the product that has more than one image

 SELECT DISTINCT *, (SELECT qc_imagens.img_nome  FROM qc_imagens
    JOIN qc_produtos on qc_produtos.pro_id = qc_imagens.img_pro_id 
         WHERE qc_imagens.img_nome <> NULL AND qc_imagens.img_excluido = '0'
        order by qc_imagens.img_id DESC
         LIMIT 1) AS nome_img 
         
         FROM qc_produtos p
 INNER JOIN qc_imagens img on img.img_pro_id = p.pro_id
 INNER JOIN qc_categorias c ON p.pro_categoria = c.cate_id
 WHERE pro_ativo = 1 AND img.img_excluido = '0'

Thanks in advance

Answer 1

Try grouping by the column p.prod_id, why then forces you to take only the first line, something like that:

SELECT  *
FROM qc_produtos p
 INNER JOIN imagens AS img ON p.pro_id = img.img_pro_id 
 INNER JOIN qc_categorias c ON p.pro_categoria = c.cate_id
 WHERE pro_ativo = 1 AND img.img_excluido = '0'
 GROUP BY p.prod_id;