Inner Join table with multiple identical Ids

Asked

Viewed 1,283 times

1

The problem is this, I have two tables one of contracts and another of 1xN borrowers and when I do the inner join it repeats exactly the amount of columns I have in borrower but only bears the name of the first. follows example:

select 
    co.id,
    ....
form 
   contratos ct
inner join
   os ss on (ss.contratos_id=ct.id)
inner join
   mutuarios mt on (mt.contratos_id=ct.id)
where....

I hope you can help me.

mysql join inner
  • 1

    publish your query so we can analyze.

  • @Augustus do not forget to accept the answer that suits him best as correct (V below the arrows on the left side)

2 answers

1


That’s the normal behavior of a INNER JOIN it makes the association of the records of one table with those equivalent in the other.

If on the table contratos you have 1 record and in the borrowers 10 records linked to this contrato, one inner join between them will show 10 records repeating the fields you placed concerning the contracts table

In your query, you are using an alias that has not been defined co. in your tables from

In your case you have the INNER JOIN between three tables, then the number of records will be the equivalence between them. In your specific case, if you have 1 contract, 3 os of that contract and 3 mutuários of this contract you should see 9 records. That is the combination between the three tables:

Take this example: http://sqlfiddle.com/#! 9/addef/3

You will see that you have for every record of A (in the example) the combination of B and C

If you want only the contract to appear, without specifying any of the records that are duplicated use the clause DISTINCT in your query:

select DISTINCT
    ct.id,
    ....
from
   contratos ct
inner join
   os ss on (ss.contratos_id=ct.id)
inner join
   mutuarios mt on (mt.contratos_id=ct.id)
where....

It is important to remember that the DISTINCT eliminate only what is duplicated for the scope of the select, that is to say ct.id, ....

0

Actually the mistake was mine, I was grouping and could not visualize the other borrowers. Thank you all for your attention

Browser other questions tagged mysql join inner

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