How to make an SQL do the correct property count under certain criteria

Asked

Viewed 111 times

-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

Only that I had to make a small implementation (I highlighted below what I added) only that the number of properties is now incorrect in the count. See below the 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,

photos.Cod

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

Inner Join photos on photos.Cod=immobles.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

These simple implementations were enough for the property count to be totally different.

I really need help!

php sql select join

  • The value is different as? Higher or lower than the value "correct", what is the relationship between photo and immovable? 1 for 1, 1 for N?

  • What is the criterion that the personnel is using to negatively? The person who negatively could inform their motives here?

  • @Rubico The value is different in the quantity of filtered properties. In the first example COUNT(immovables.id) Immovable AS displays perfectly the number of properties. In the second example, just because I made the small implentations the COUNT(immovel.id) Immovables displays totally different values, ie incorrect.

  • without a knowledge of your bank is a little difficult, but I noticed that you are comparing the foto.cod with the imovel.id, would not be with the imovel.cod? Try to show what is the structure of your bank, what are the relationships.

  • What you have valved is now valid. As there is a relationship between photo properties (where a property can have several photos), it is necessary that, in the table of photos, there is a column to store which property is the "owner" of the photo, that is, in the table of photos there must be a column fotos.imovel_cod, for example, and it should be through this column that the inner join be done ( INNER JOIN fotos ON fotos.imovel_cod = imovel.cod ). How is the structure of the table photos? There is this column to make the relationship between photos and real estate?

1 answer

0

When you do

inner join fotos on fotos.cod=imoveis.id

you say that for each immovable.id there is a photo with corresponding.Cod photos. If your photo table doesn’t have a photo for each immoble, the number of lines in the query with Inner Join will be lower.

If you want to bring immobles with or without photos, your new joint should be an Outer Join.

left outer join fotos on fotos.cod=imoveis.id

Browser other questions tagged php sql select join

You are not signed in. Login or sign up in order to post.