INNER JOIN is duplicating record

Asked

Viewed 3,337 times

-3

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

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 this is happening?

Thanks.

php sql join
  • 2

    Show me the keys of the tables, put the definition of each one there.

  • 2

    I imagine that if there are N photos by immovable, soon will repeat his information for photo, can explain better the context of the code?

  • Take a test ! puts select * from account Inner Join immobles on account.id = immovable.Cod Inner Join photos on photos.Cod = immovable.id; , and checks if there are actually duplicate lines ( all returned fields must be identical , have something different if not a select distinct would work )

3 answers

3

Analyzing the structure I believe it to be as follows

(account) 1 - N (immovable) 1 - N (photos)

Where we have an account with several properties, and one property has several photos. With Inner Join at no time will be able to repeat the records, what may be happening is that for each record of the table photo is bringing the records of the table real, is what should happen because for each record of "Photo" there is one of "Immovable".

0

If your data is actually duplicated in the tables you can use the 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

  • The distinct did not resolve

  • Are all columns duplicated ? and which database?

  • 2

    Who came back against has some justification?

  • @gladison-Neuza-perosini it is very likely that the data is not duplicated, because the distinct of our friend marconcilio-souza would solve the problem.

0

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

Possible problem you’re having:

  1. holds an Account for multiple properties

  2. own a property for several photos

    select c.id,c.dominio,c.modelo, i.Cod,i.id,i.title,i.vvenda,i.vtemporada,i.vanual, i.tipo,i.Descricao,i.cidade,i.data,i.endereco, i.dormitorio,i.banheiro,i.areatotalmedida from conta c Inner Join imovel i on c.id=i.Cod

  3. Check for data duplication

    a) select without Distinct

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

2) There is duplicity a) export to excel and Filtre only duplicates the remaining delete...so you will view why it contains more than one result for the same ID/COD

3) Do the same with table photos and immovable a) I believe that there is more than one photo for the same property, so duplicity occurs

Possible solution for table pictures and immovable:

1) make a query for accounts and real estate, then recover the ids and make a second select to rescue all the photos from the real estate

I hope I gave you a north.

  • 2

    Please report a better idea who gave negative

  • Just a Badstorm, okay? Probably: 1) because it is an extremely vague question and without details necessary to present a proper answer; 2) because its formatting is a bit confusing; 3) because you pointed north and the person was looking south.

  • Dear @brasofilo please help us solve this problem with your illustrious answer, because I understand there is no answer or attempt to help comrade gladison

  • I did not deny your answer. I’m trying to explain the possible reasons for these vows. obviously, trying to help can be mistaken for offense... excuse me,

Browser other questions tagged php sql join

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