Use LIMIT together with LEFT JOIN in more than one table

Question

Use LIMIT together with LEFT JOIN in more than one table

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I have a anuncios table, in which I want to list the anuncios of the user together with the imagens , I'm trying with the query below, but if I have 5 imagens registered in anuncio , in the table it will display 5 times, can you limit the images?

Query :

"select a.*, u.nome as nome_usuario, i.thumb as nome_imagem from anuncios a 
 left join usuarios u on u.id = a.id_usuario left join imagens i on 
 i.id_anuncio = a.id where id_usuario = '{$idUsuario}'";

php mysql

asked by anonymous 20.02.2015 / 04:02

2 answers

1

I try to use the clause at the end of your query:


GROUP BY u.id_usuario, i.thumb

It would look something like this:


SELECT a.*,
       u.id_usuario,
       u.nome AS nome_usuario,
       i.thumb AS nome_imagem 
FROM   anuncios a
LEFT JOIN usuarios u ON (u.id = a.id_usuario)
LEFT JOIN imagens i ON (i.id_anuncio = a.id)
WHERE  id_usuario = :id_usuario 
GROUP BY u.id_usuario, i.thumb;

29.06.2015 / 16:01

in R, create a vector rounding function so that the coordinates are 100 Return Qt during release

score 1 · Accepted Answer

Take your query, make it a subquery, and apply LIMIT on the "parent" query, something like the example below that will only bring 1 record:

    select * FROM ( 
      select a.*, 
     u.nome as nome_usuario, 
     i.thumb as nome_imagem from anuncios a 
    left join usuarios u on u.id = a.id_usuario 
    left join imagens i on i.id_anuncio = a.id 
      where id_usuario = '{$idUsuario}' 
) 
      LIMIT 1;