How to make an INNER JOIN does not repeat information

Question

How to make an INNER JOIN does not repeat information

Navigation

#1 by (1 votes)
#2 by (0 votes)

6

I have 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";

It repeats the property twice even using GROUP BY . Since there is no such information in the database, what do I do?

php inner

asked by anonymous 14.03.2016 / 17:51

2 answers

How do I log in to a desktop application via a web application? Modifying an item from a list shared by multiple threads

score 1 · Answer 1

When your query uses an aggregate function, you should put all 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 report an error in your query, I suppose so. MySQL accepts non-default "SQL shortcuts" such as omitting GROUP BY fields or the entire clause. In this case, I think you can achieve the result you want by using only the key fields of clients and real estate.

GROUP BY clientes.id, imoveis.id

score 0 · Answer 2

You must perform group by the field "imoveis.id" 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";