INNER JOIN is duplicating record [closed]

Question

INNER JOIN is duplicating record [closed]

Navigation

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

-3

I have an INNER JOIN that is duplicating records in the view. Because in the database such records are not duplicates.

select 
 conta.dominio,
 conta.id,
 conta.modelo,
 imoveis.id,
 imoveis.cod,
 imoveis.titulo,
 imoveis.vvenda,
 imoveis.vtemporada,
 imoveis.vanual,
 imoveis.tipo,
 fotos.cod,
 fotos.foto,
 imoveis.descricao,
 imoveis.cidade, 
 imoveis.data, 
 imoveis.endereco, 
 conta.nome, 
 imoveis.dormitorio, 
 imoveis.banheiro, 
 imoveis.atotal, 
 imoveis.areatotalmedida 

  from 
   conta 

   inner join imoveis on 
    conta.id = imoveis.cod 

   inner join fotos on 
    fotos.cod = imoveis.id;

How to solve this question? Why is this happening?

Thank you.

php sql join

asked by anonymous 23.02.2016 / 15:55

3 answers

Validate CPF using WPF [closed] SQL error while trying to make an UPDATE with PHP [closed]

score 3 · Answer 1

Analyzing the structure, I believe it is as follows

(account) 1 - N (buildings) 1 - N (photos)

Where we have an account with several properties, and a property has several photos. With Inner Join in no time you can repeat the records, what may be happening is that for each record of the table photo is bringing the records of the table immobile, is what must happen because for each record of "Photo" there is one of "Property ".

score 0 · Answer 2

If your data is actually duplicated in the tables you can use distinct ,

select distinct conta.dominio,conta.id,conta.modelo,imoveis.id,imoveis.cod,imoveis.titulo,imoveis.vvenda,imoveis.vtemporada,imoveis.vanual,imoveis.tipo,fotos.cod,fotos.foto,imoveis.descricao,imoveis.cidade, imoveis.data, imoveis.endereco, conta.nome, imoveis.dormitorio, imoveis.banheiro, imoveis.atotal, imoveis.areatotalmedida from conta inner join imoveis on conta.id = imoveis.cod inner join fotos on fotos.cod=imoveis.id

score 0 · Answer 3

I already had this problem, and I used the breakdown of the queries to understand its unsatisfactory result to what I wanted. I direct you to do the following:

Possible problem you're having:

Own an Account for Multiple Real Estate

Own a property for multiple photos

select c.id, c.domain, c.model, i.cod, i.id, i.titulo, i.vvenda, i.vtemporada, i.vanual, i.type, i.description, i.city, i.data, i.endereco, i.dormitorio, i.banheiro, i.atotal, i.areatotmeasure from account c inner join imoveis i on c.id = i.cod

Check for duplicate data

a) make a select without Distinct

b) open another window in your database and do with Distinct and analyze if it has duplicity

2) Double occurrence a) export to excel and filter only the duplicates the rest delete ... so you will see why it contains more than one result for the same ID / COD

3) Do the same with the table pictures and real estate a) I believe that there is more than one photo for the same property, this way there is duplicity

Possible solution for the photos and real estate table:

1) make a query for accounts and real estate, then retrieve the ids and make a second select to redeem all real estate photos

I hope I have given you a north.