How to make a SQL do the correct count of real estate under certain criteria

Navigation
-1

I have the following SQL that correctly counts the number of properties: 

select
clientes.id,
clientes.nome, 
clientes.status,
clientes.cliente,
clientes.tipo,
clientes.disponibilidade,
imoveis.id,
imoveis.cod,
imoveis.status,
imoveis.vvenda, 
COUNT(imoveis.id) AS imoveis
from clientes
inner join imoveis on clientes.cliente = imoveis.cod 
where 
imoveis.status='2'
AND clientes.status='2' 
AND imoveis.vvenda < clientes.disponibilidade
AND imoveis.vvenda <> '0'
AND clientes.cliente = '$cliente'
AND imoveis.cod = '$cliente'
GROUP BY clientes.id

I just needed to make a small implementation (I highlighted below what I added), but the number of properties is now incorrect in the count. See below for SQL with implementations: 

select
clientes.id,
clientes.nome, 
clientes.status,
clientes.cliente,
clientes.tipo,
clientes.disponibilidade,
imoveis.id,
imoveis.cod,
imoveis.status,
imoveis.vvenda, 
COUNT(imoveis.id) AS imoveis,
  

fotos.cod 

from clientes
inner join imoveis on clientes.cliente = imoveis.cod
  

inner join photos on fotos.cod = imoveis.id 

where 
imoveis.status='2'
AND clientes.status='2' 
AND imoveis.vvenda < clientes.disponibilidade
AND imoveis.vvenda <> '0'
AND clientes.cliente = '$cliente'
AND imoveis.cod = '$cliente'
GROUP BY clientes.id

Just these simple implementations for real estate counting would be totally different.

I really need some help!

    
asked by anonymous 16.03.2016 / 12:41

1 answer

0

When you do 

inner join fotos on fotos.cod=imoveis.id

You say that for each imoveis.id there is a photo with corresponding photo.cod. If your photo table does not have a photo for each property, the number of rows in the inner join query will be smaller.

If you want to bring properties with or without photos, your new join should be an outer join. 

left outer join fotos on fotos.cod=imoveis.id
    
16.03.2016 / 15:33
Doubt how to loop in JavaScript Doubt with DATEDIFF days range between dates