Select Group By Having In (SELECT), HELP !

[ssteinke]

This is driving me nuts guys...

I need a query to show only those Company's that have a relationship to ALL Departments. These are the tables:

tblCompanies
CompanyID

tblUserDepts
UserDeptID

tjxCompanySubscriptions
CompanyID
UserDeptID

tjxCompanySubscriptions is a junction table that defines which Department has a subscription to a Company.

I need to know which Company's are subscribed by ALL Departments.

Probably simple, but not for me!

Scott

 

[SamDeMan]

you need a query with an outer join. join tblUserDepts with tjxCompanySubscriptions then count tjxCompanySubscriptions.UserDeptID and do where countOfUserDeptID = 10

good luck,

sam

 

[ssteinke]

Thanks for the reply Sam, it got me thinking in the right direction... this is what I FINALLY came up with,

SELECT CompanyID
FROM tjxCompanySubscriptions
GROUP BY CompanyID
HAVING Count(*)=(SELECT Count(*)
FROM tblUserDepts);

Scott