Limit Result of Inner Join

To filter repeated results in a join you can use group by . For example, let's suppose that the table that has duplicate records for the same property is imagem_imovel :

Inthiscase,thereisonlytheproperty"1" with two images: img1 and img2. To retrieve the largest image, you will need to create a subquery in your join:

select * from imovel join bairro using (broCodigo) join tipo using (tpoCodigo) join (select max(imvNome), imvCodigo from imagem_imovel group by imvCodigo) maioresImagens using (imvCodigo);

Notice that instead of joining the table imagem_imovel a join was done with a query that returns the largest names in that table (max (imvName)) for each property (group by imvCode). The rest of the query remains and now the result will be:

The important thing here is to know how to use subqueries whenever you have a limitation on the results of a join.

In this case, I think it would only fit one sub-select:

SELECT 
  'imovel'.'imvValorImovel', 
  'imovel'.'imvCodigo', 
  'imovel'.'broCodigo', 
  'imovel'.'imvDormitorio', 
  'imovel'.'imvSuite', 
  'imovel'.'tpoCodigo', 
  'imovel'.'imvGaragem', 
  'bairro'.'broNome', 
  'tipo'.'tpoNome',
   (SELECT  
     'imagem_imovel'.'imaNome'
    FROM 'imagem_imovel' 
    WHERE 'imagem_imovel'.'imvCodigo' = 'imovel'.'imvCodigo' 
    ORDER BY 'imagem_imovel'.'imvCodigo'  
    LIMIT 1) as 'imaNome'
FROM  'imovel' 
INNER JOIN  'bairro' ON  'imovel'.'broCodigo' =  'bairro'.'broCodigo' 
INNER JOIN  'tipo' ON  'imovel'.'tpoCodigo' =  'tipo'.'tpoCodigo'
INNER JOIN  'bairro' ON  'imovel'.'broCodigo' =  'bairro'.'broCodigo'
WHERE  'imovel'.'broCodigo' = "'.$resultado->broCodigo.'" 
AND 'imovel'.'tpoCodigo' = "'.$resultado->tpoCodigo.'" 
AND 'imovel'.'imvFinalidade' = "'.$resultado->imvFinalidade.'"
LIMIT 3