Table JOIN Query

[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.

 

[Gooser]

Have you tried:

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

?

Gooser

 

[Naoise]

I have indeed, that will list only the Treatments associated with a TeamMember. I need a list of all Treatments and their ProductTypes and a way of knowing which TeamMembers are linked with those Treatments. Any ideas where my sql is going wrong, I feel I'm a join away from the right thing

 

[SajidAttar]

Try following query .. .(I have not checked the query as I dont have data.)

SELECT	ISNULL( M.TeamMemberID, '')  TeamMemberID,
	T.TreatmentID TreatMentID,
	ISNULL( P.ProductType, '') ProductType,
	T.Treatment Treatment

FROM tblTreatment T (NOLOCK)
LEFT OUTER JOIN tblProductTypeTreatment PT (NOLOCK) 
	INNER JOIN tblProductType P (NOLOCK) ON PT.ProductTypeID = P.ProductTypeID
	INNER JOIN tblTeamMemberTreatments M (NOLOCK) ON PT.TreatmentID = M.TreatmentID
  ON PT.TreatmentID = T.TreatmentID

It will be good if you post the script to create the temp table with some sample data..


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[r937]

please do not post the same question more than once

i believe the question was adequately answered in the ANSI SQL forum

r937.com | rudy.ca