SQL Server - Nested Query

Question

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!

What course are you taking? There is no doubt this is a homework assignment. — jeph perro
– jeph perro, Commented Nov 9, 2009 at 21:57
I promise on everything that it is not homework. I simply get lost on nested joins. — user70192
– user70192, Commented Nov 9, 2009 at 21:58
Nested is not the correct term to use here. This turns out to be a very nice query. — ChaosPandion
– ChaosPandion, Commented Nov 9, 2009 at 22:06

ChaosPandion · Accepted Answer · 2009-11-09 21:55:59Z

4

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)

answered Nov 9, 2009 at 21:55

ChaosPandion

78.4k18 gold badges122 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

K Richard · Accepted Answer · 2009-11-09 22:11:28Z

0

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)

answered Nov 9, 2009 at 22:11

K Richard

2,0042 gold badges24 silver badges43 bronze badges

Collectives™ on Stack Overflow

SQL Server - Nested Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related