Oracle Left join returns more records than in left table

user3050151 0
Viewed 21

I am joining couple of Oracle tables on 2 separate fields. If first join fails, then I join on a second field. I am using left joins However when doing the second join, I am getting a lot more records, most likely because there are multiple values found in table 2

Any suggestions how I can remove duplicates on the 2 joins I am doing.

Thanks

select v.* from
(select 
t.*
from 
(select x.* from table1 x
MINUS
select x.* from table1 x
join table2 u
on x.postcode = u.postcodelocator) t) v

left join from table2 w
on v.AL1_POST_TOWN_NAME = w.TOWNNAME
left-join oracle
1 Answers

You use:

SELECT v.*
FROM   (...) v
       LEFT OUTER JOIN table2 w ON ...

You never return any values from table2 as you SELECT v.* so it does not matter if the LEFT OUTER JOIN matches or not as the JOIN will not remove any rows from the driving table and will duplicate rows in the driving table if there are duplicates in the joined column in the outer joined table.

So, if you want to remove the duplicates from the JOIN then just remove the OUTER JOIN and use:

SELECT v.*
FROM   (...) v

Which then looks like you could simplify the entire query down to:

SELECT DISTINCT *
FROM   table1 t1
WHERE  NOT EXISTS (SELECT 1
                   FROM   table2 t2
                   WHERE  t1.postcode = t2.postcodelocator)
MT00
Related