Use LIMIT together with LEFT JOIN in more than one table

Question

I have a table anuncios, in which I wish to list the anuncios of the user together with the imagens, I’m trying with the query below, but if you have 5 imagens registered in the anuncio, in the table it will display 5 times, has to 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}'";

Answer 1

Experiment use at the end of your query the clause:

GROUP BY u.id_usuario, i.thumb

Would 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; 

Answer 2

Take your query, turn it into a subquery and apply LIMIT in the "parent" query, something like the example below that will bring only 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;