CODEWITHSUNDEEP
sqloracle-databasejoinouter-join
Jaxox
Jaxox

Reputation: 968

Incorrect results when using two outer joins

Somehow the record is 10x more using a double left outer join than the original p table. What is the correct syntax for (A left join b) left join C?

Here is what I have so far:

SELECT COUNT(*)
FROM p
LEFT OUTER JOIN rate ON p.nip = rate.nip
LEFT OUTER JOIN n ON p.pin = n.pin

UPDATE

is it possible to join using to column? 

SELECT COUNT(*)
FROM p
LEFT OUTER JOIN rate ON p.nip = rate.nip
LEFT OUTER JOIN n ON p.pin = n.pin and p.col2 = n.col2;

Upvotes: 0

Views: 52

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

That syntax looks correct.

The problem is likely that you have more than one rate record per p record for a given npi, or that you have more than one n record per p record for a given pin, or a combination of both. In that situation, the database is supposed to show you all the combinations, even if it means duplicating records in the p table.

You fix it by adding additional conditional expressions to the joins, such that only record record in the additional tables meets the criteria.

Upvotes: 3

Related Questions