INNER JOIN with ORDER BY

Question

INNER JOIN with ORDER BY

Navigation

#1 by (3 votes)

3

In MYSQL I have two tables, product and product_photo.

In the product_foto table there is a field that calls first , which normally has its value 0. In the site administrative system there may be a marking of which photo will be the first (main) strong> first is set to 1.

Table Product:

TablePhoto_Product:

When listing all products on the site, only one picture of each product is displayed. And I wanted it to be just the photo where the first field equals 1 .

I've used select:

SELECT * FROM produto 
INNER JOIN produto_foto ON produto_foto.idproduto = produto.id
GROUP BY produto.id
ORDER BY produto.id DESC, primeira_foto.primeira DESC

Expected result:

chupeta - fotoX.jpg

caderno - fotoX.jpg

tesoura - foto.jpg (nesse caso não existiu marcação de qual é a foto 1, então pegaria o registro de menor ID da tabela Produto_Foto)

mysql order-by

asked by anonymous 24.06.2016 / 16:32

1 answer

Clang presents character error too large but Visual Studio compiles normal Find specific day of the week between dates

score 3 · Accepted Answer

Friend, try this.

SELECT p.*, pf.* 
  FROM produto p
 INNER JOIN produto_foto pf ON p.id = pf.idproduto
 INNER JOIN ( SELECT id
                FROM produto_foto 
               WHERE primeira = 1
               UNION
              SELECT MIN(id) AS id
                FROM produto_foto
               GROUP BY idproduto HAVING SUM(primeira) = 0 ) i ON pf.id = i.id

An example to run here: link