How to make an INNER JOIN not repeat information

Question

I got the following SQL INNER JOIN:

$sqlxml = "
SELECT 
clientes.id, clientes.nome, clientes.status, clientes.cliente, clientes.tipo,
clientes.disponibilidade, imoveis.id, imoveis.cod, imoveis.status, imoveis.vanual, 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.vanual < clientes.vanual AND imoveis.vanual <> '0' AND clientes.cliente = '$cliente' 
GROUP BY clientes.id";

Him repeats the property twice even using GROUP BY. Since there is in the database this information repeated, what do?

Answer 1

When your query uses an aggregation function, you should put all the other SELECT fields in a GROUP BY clause.

Your query should be

SELECT 
    clientes.id, clientes.nome, clientes.status, 
    clientes.cliente, clientes.tipo, clientes.disponibilidade, 
    imoveis.id, imoveis.cod, imoveis.status, imoveis.vanual, 
    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.vanual < clientes.vanual 
    AND imoveis.vanual <> '0' 
    AND clientes.cliente = '$cliente' 
GROUP BY clientes.id, clientes.nome, clientes.status, 
    clientes.cliente, clientes.tipo, clientes.disponibilidade, 
    imoveis.id, imoveis.cod, imoveis.status, imoveis.vanual

You did not indicate if your database server is Mysql, but if the server did not error in your query I imagine so. Mysql accepts "shortcuts" in SQL that are non-standard, such as omitting GROUP BY fields or the full clause. In this case, I think you can achieve the result you want using only the key fields of customers and immovable.

GROUP BY clientes.id, imoveis.id

Answer 2

You should perform group by by the "immovel.id" field Ex:

$sqlxml = "
SELECT 
clientes.id, clientes.nome, clientes.status, clientes.cliente, clientes.tipo,
clientes.disponibilidade, imoveis.id, imoveis.cod, imoveis.status, imoveis.vanual, 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.vanual < clientes.vanual AND imoveis.vanual <> '0' AND clientes.cliente = '$cliente' 
GROUP BY imoveis.id";