Limiting Results from Multiple Left Joins

[vacorama]

hey,
i'm performing a few LEFT JOINS based on one table. I'm running into trouble getting results from one of the joined tables, in the example below it's the address table. When i remove "Address.AddressType='Home'" from the ON clause, i get the data, only i have duplicate rows (well, not really duplicates, but one row for every address where many customers have 2 addresses... I feel like im hitting a brick wall with this thing, and not sure as to what to try next, does anyone notice anything im doing wrong here? Would greatly appretiate any help/ideas...

SELECT L.*, C.*, Address.*
FROM `Loan` AS L LEFT JOIN (
`Customer` AS C
) ON (
L.CustomerID = C.CustomerID
) LEFT JOIN (
`Address` AS Address
) ON (
Address.AddressType='Home' AND Address.CustomerID = L.CustomerID
)

[FalseDawn]

Your last join condition looks suspect - are you sure it shouldn't be a left join from customer to address, not address to loan?

Draw a diagram - it will help you.

Also move "Address.AddressType='Home' " to a WHERE clause from the JOIN clause.

[edited by: FalseDawn at 2:04 am (utc) on Nov. 16, 2006]