SQL Server - Nested Query

Question

Hello,

I have a query that I'm not sure how to write. I'm not a SQL expert and it's pretty nasty. I'm hoping someone here can help me with it.

I have a table called "Members" which has a list of user names to my web site. I need to get the list of users that belong to one or more divisions in my company as well as one or more managers. The specific divisions and managers are chosen by a user in my web site. I have a list of the managers and divisions they have selected. I have also parsed them into a comma-delimited list. Here is a summary of the table information I am trying to link together:

Members

• UserName
• StoreID

Store

• StoreID
• PostalCode

Division

• PostalCode
• ManagerID

Manager

• ManagerID
• ManagerName

How do I get the list of Members based on a list of Regions and Managers that a user chooses? Sincerely thank you for your help!

Answer 1

Select Members.UserName
From Members
    Join Store On Members.StoreID = Store.StoreID
    Join Division On Store.PostalCode = Division.PostalCode
    Join Manager On Division.ManagerID = Manager.ManagerID
Where Division.PostalCode In (12345, 12346)
    And Manager.ManagerID In (1, 2, 3, 4)

Answer 2

You say you have a list of managers and divisions. Do you have ManagerID and PostalCode? If so, you don't need to join in the manager table as both of these are found in the division table.

SELECT
    Members.UserName
FROM
    Members
    INNER JOIN
    Store
    ON
     Members.StoreID = Store.StoreID
    INNER JOIN
    Division
    ON
     Store.PostalCode = Division.PostalCode
WHERE
    Division.PostalCode IN (Use comma delimited postal codes here)
    AND
    Division.ManagerID IN (Use comma delimited manager ids here)