Yet Another Outer Join Problem

[GKWilly]

Hi Guys,

Been reading through the posts on this topic but can't find the answer.
I have the usual one to many relationships and want to return each one record with one of the many if it exists.

Each record has a possible two fee types.

The sql I have is like this:

select distinct t2.s_reference, t3.e_reference ,t3.e_name,
t4.f_type

from t1 left outer join t2 on t2.s_id=t1.p_id
inner join t3 on t1.p_id=t3.e_stude
left outer join t5 on t3.e_id=t5.ef_feeenrol
left outer join t4 on t5.ef_id=t4.f_id and t4.f_type='tui'

What happens is that I end up with 2 rows for each record, one with null fee type and a record for the 'tui' fee type.

Im I going mad as I can't see the reson for the duplication!!!

Many thanks in advance

 

[vongrunt]

> What happens is that I end up with 2 rows for each record, one with null fee type and a record for the 'tui' fee type.

That's probably because of condition in outer join. If row in t4 matches by ef_id, but f_type is not equal 'tui', row with NULLs is returned.

How about inner join between t5 and t4, and then left outer join between t3 and (t5 and t4 together)?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

Left join will return NULLs for records that don't exists change your left join to an inner join

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[GKWilly]

Thanks vongrunt. I thought it's be something simple but been staring at it for hours and couldn't see the wood for the trees.