Limit Inner Join Result

Question

I’ve been in trouble for a few days. I have a select that does some Inners and should return 3 lines, but one of the Inners has several results linked to the searched result; how to limit this Inner to take only the first result and select continue returning the 3 lines?

    SELECT `imovel`.`imvValorImovel`, `imovel`.`imvCodigo`, 
          `imovel`.`broCodigo`, `imovel`.`imvDormitorio`, `imovel`.`imvSuite`, 
          `imovel`.`tpoCodigo`, `imovel`.`imvGaragem`, `bairro`.`broNome`, 
          `tipo`.`tpoNome`, `imagem_imovel`.`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`
    INNER JOIN  `imagem_imovel` ON  `imovel`.`imvCodigo` =  `imagem_imovel`.`imvCodigo 
    WHERE  `imovel`.`broCodigo` = "'.$resultado->broCodigo.'" 
    AND `imovel`.`tpoCodigo` = "'.$resultado->tpoCodigo.'" 
    AND `imovel`.`imvFinalidade` = "'.$resultado->imvFinalidade.'"
    LIMIT 3

Answer 1

To filter repeated results on a Join you can use a group by. For example, let’s assume that the table that has duplicated records for the same property is the imagem_imovel:

In this case, there is only the property "1" with two images: img1 and img2. To recover 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 doing Join on the table imagem_imovel was made a Join with a query that returns the biggest names of this table (max(imvNome)) for each property (group by imvCodigo). The rest of the query remains and now the result will be:

The important thing here is to know how to make use of the Queries whenever you have a limitation on the results of a Join.

Answer 2

in that case, I think it would fit only a 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