Table JOIN 1

[Naoise]

tblTeamMemberTreatments
TeamMemberID | TreatmentID

tblProductTypeTreatments
TreatmentID | ProductTypeID


tblProductTypes
ProductTypeID | ProductType


tblTreatments
TreatmentID | Code | Treatment | ActiveFlag


How can I get a single sql query to return a list of all treatments and which of those
treatments a TeamMember partakes in, I need the ProductType also. The results table should list :

TeamMember | Treatment | ProductType

At the moment I have

SELECT isNull(TeamMemberID,0) as 'TeamMemberID', isNull(a.TreatmentID,0) as 'TreatmentID',ProductType, Treatment
FROM tblTreatments a
LEFT OUTER JOIN tblTeamMemberTreatments b ON a.TreatmentID = b.TreatmentID
LEFT OUTER JOIN tblProductTypeTreatments c ON b.TreatmentID = c.TreatmentID
LEFT OUTER JOIN tblProductTypes d ON c.ProductTypeID = d.ProductTypeID
ORDER BY ProductType

Which unfortunately lists the ProductType as NULL if a TeamMember does not partake. Any help appreciated.

 

[PHV]

Have you tried this ?
FROM tblTreatments a
LEFT OUTER JOIN tblProductTypeTreatments c ON [!]a[/!].TreatmentID = c.TreatmentID
LEFT OUTER JOIN tblProductTypes d ON c.ProductTypeID = d.ProductTypeID
LEFT OUTER JOIN tblTeamMemberTreatments b ON a.TreatmentID = b.TreatmentID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Thanks, that's exactly it.