Need some quick SQL help! left/right/inner joins

[robphelan]

Hey everyone.. I'm trying to figure out this seemingly simple join over 3 tables. They are related as:

Header
Detail
Other_Detail

Header.key_field = Detail.key_field1
Detail.key_field1 = Other_Detail.key_field1
Detail.key_field2 = Other_Detail.key_field2

The only problem is that Other_Detail may or may not have any data for the key_field combinations.

I need the records returned if there is anything in the Header / Detail tables.

I thought I had it.. but i'm only getting return values if data exists in all 3 tables.

thanks a bunch.
RP

 

[robphelan]

looks like i need an inner join on Header / Detail, then a Left Inner join on Detail / Other_Detail ?

 

[robphelan]

ok.. nevermind.. I cheated.

I created one view as an inner join on Header / Detail and then a second view on View#1 left inner join + Other_Detail.

 

[Markbnj]

You just need a left outer join to preserve the data in the header and detail tables when there are no records in the dependent table, i.e.

select a.*, b.*, c.* from
header a
join detail b on a.key_field = b.key_field1
left join other_detail c on b.key_field1 = c.key_field2

 

[Albatross]

Take a look here for some easy hints.

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/