Link to home
Get AccessLog in
Avatar of saabStory
saabStoryFlag for United States of America

asked on

Need help filtering a query with a left outer join

I have 2 tables - 1 for divisions and the other for sub-divisions.  The division table contains our corporate divisions and is not editable by anyone outside of our development team.  The sub-division table is part of a larger application and in this sense, it allows different groups throughout the company to assign different sub-divisions to the corporate divisions.  Thus, different groups will have different entries depending on terminology, markets, strategies, etc.  To add to the mix (and the reason for the join), not all divisions will always have sub-divisions.

The query below works dandy for the single group that is in there right now but the second I add a where clause, to filter out the groupIDs from the sub-division table, the query will only return records that have a groupID.  What I need is to get all the divisions and all the subdivisions that belong to the same group.  Even if there aren't any sub-divisions present for a given division, I still need to see all the divisions.  Hope that makes sense.

So the question is, based on the query below, how do I limit the records returned to a single groupID?  Schema and query below.  Thank you very much in advance.

tbl_CorporateDivisions
-------------------------
divisionID (pk)
divisionName

sam_MediaSubDivisions
---------------------------
subDivisionID (pk)
groupID
divisionID
subDivisionName

Query
-------
SELECT     dbo.tbl_CorporateDivisions.divisionID, dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionID, dbo.sam_MediaSubDivisions.subDivisionName
FROM         dbo.tbl_CorporateDivisions LEFT OUTER JOIN
                      dbo.sam_MediaSubDivisions ON dbo.tbl_CorporateDivisions.divisionID = dbo.sam_MediaSubDivisions.divisionID
ORDER BY dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionName
Avatar of Duane Lawrence
Duane Lawrence
Flag of United States of America image

SELECT     dbo.tbl_CorporateDivisions.divisionID, dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionID, dbo.sam_MediaSubDivisions.subDivisionName
FROM         dbo.tbl_CorporateDivisions
LEFT OUTER JOIN  dbo.sam_MediaSubDivisions
ON dbo.tbl_CorporateDivisions.divisionID = dbo.sam_MediaSubDivisions.divisionID
where sam_MediaSubDivisions.groupID = 11
ORDER BY dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionName

or

SELECT     dbo.tbl_CorporateDivisions.divisionID, dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionID, dbo.sam_MediaSubDivisions.subDivisionName
FROM         dbo.tbl_CorporateDivisions
LEFT OUTER JOIN  dbo.sam_MediaSubDivisions
ON dbo.tbl_CorporateDivisions.divisionID = dbo.sam_MediaSubDivisions.divisionID
where sam_MediaSubDivisions.groupID = @variable
ORDER BY dbo.tbl_CorporateDivisions.divisionName, dbo.sam_MediaSubDivisions.subDivisionName
ASKER CERTIFIED SOLUTION
Avatar of lawarner
lawarner

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of saabStory

ASKER

That did the trick - good job - thanks a lot!