Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 confusing join

Author  Topic 

boybles
Starting Member

23 Posts
Posted - 2007-07-26 : 09:21:10
I'm stuck trying to pull together what is for me a complex join statement. I have 8 tables following, and would like to figure out how to extract all users and roles (if they have any associated)
when given a company and division. I have given a couple examples the way they should be output.


RoleTbl
----------------
RoleID|RoleName
----------------
1|Admin
2|User
3|Guest


RoleDivisionTbl
---------------
RoleID|DivisionID
-----------------
1|1
2|1
3|1
2|2

DivisionTbl
----------------
DivisionID|DivisionName
----------------
1|Accounting
2|Marketing
3|Sales

CompanyDivisionTbl
----------------
CompanyID|DivisionID
----------------
1|1
1|2
1|3
2|2

CompanyTbl
----------------
CompanyID|CompanyName
----------------
1|Gap
2|MayCo
3|Sears

UserRoleTbl
----------------
UserID|RoleID
----------------
1|1
2|3
3|2
6|2

UserTbl
----------------
UserID|UserName
1|Jim
2|Frank
3|Kelly
4|Al
5|James
6|Tom
7|George

CompanyUserTbl
----------------
CompanyID|UserID
----------------
1|1
1|2
1|3
1|4
1|5
2|6
2|7

Ex1:
If Company=1 and Division=1
Output:
UserID|UserName|RoleID|RoleName
1|Jim|1|Admin
2|Frank|3|Guest
3|Kelly|2|User
4|Al|Null|Null
5|James|Null|Null

Ex2:
If Company=2 and Division=2
Output:
UserID|UserName|RoleID|RoleName
6|Tom|2|User
7|George|Null|Null


Please help!!! Thanks!!!
Boybles

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-26 : 11:37:21
[code]-- Prepare sample data
DECLARE @RoleTbl TABLE (RoleID INT, RoleName SYSNAME)

INSERT @RoleTbl
SELECT 1, 'Admin' UNION ALL
SELECT 2, 'User' UNION ALL
SELECT 3, 'Guest'

DECLARE @RoleDivisionTbl TABLE (RoleID INT, DivisionID INT)

INSERT @RoleDivisionTbl
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 2, 2

DECLARE @DivisionTbl TABLE (DivisionID INT, DivisionName SYSNAME)

INSERT @DivisionTbl
SELECT 1, 'Accounting' UNION ALL
SELECT 2, 'Marketing' UNION ALL
SELECT 3, 'Sales'

DECLARE @CompanyDivisionTbl TABLE (CompanyID INT, DivisionID INT)

INSERT @CompanyDivisionTbl
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 2

DECLARE @CompanyTbl TABLE (CompanyID INT, CompanyName SYSNAME)

INSERT @CompanyTbl
SELECT 1, 'Gap' UNION ALL
SELECT 2, 'MayCo' UNION ALL
SELECT 3, 'Sears'

DECLARE @UserRoleTbl TABLE (UserID INT, RoleID INT)

INSERT @UserRoleTbl
SELECT 1, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 6, 2

DECLARE @UserTbl TABLE (UserID INT, UserName SYSNAME)

INSERT @UserTbl
SELECT 1, 'Jim' UNION ALL
SELECT 2, 'Frank' UNION ALL
SELECT 3, 'Kelly' UNION ALL
SELECT 4, 'Al' UNION ALL
SELECT 5, 'James' UNION ALL
SELECT 6, 'Tom' UNION ALL
SELECT 7, 'George'

DECLARE @CompanyUserTbl TABLE (CompanyID INT, UserID INT)

INSERT @CompanyUserTbl
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 7

-- Show the expected output
SELECT cu.UserID,
u.UserName,
ur.RoleID,
r.RoleName
FROM @CompanyDivisionTbl AS cd
INNER JOIN @CompanyUserTbl AS cu ON cu.CompanyID = cd.CompanyID
INNER JOIN @UserTbl AS u ON u.UserID = cu.UserID
LEFT JOIN @UserRoleTbl AS ur ON ur.UserID = u.UserID
LEFT JOIN @RoleTbl AS r ON r.RoleID = ur.RoleID
WHERE cd.CompanyID = 1
AND cd.DivisionID = 1[/code]

E 12°55'05.76"
N 56°04'39.42"
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-07-26 : 11:39:28
Dude, you have waaaaaaaay to much time on your hands

boybles: That is how to post a question



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-26 : 11:43:02
Sprock...
Please rephrase question. Bot does not understand

Just kidding:
Today I have put in glass window in toliet door, cut doors to fit new floor, changed brake plates and brake disc on our second car, picked berries and made lemonade.
And painted living room with two coates of paint. And installed our new oven and glass induction stove.

And answered some posts here at SQLTeam.


E 12°55'05.76"
N 56°04'39.42"
Go to Top of Page

boybles
Starting Member

23 Posts
Posted - 2007-07-26 : 14:09:22
Peso,
Thank you so much for your help on this. I'm really trying to grasp the concepts of joins and am a very visual/example oriented person, so your time and help is immensely appreciated. I've tried your suggestion, and it checks out for the examples I posted. But when checking against the RoleDivisionTbl, something is not working. In the example, there are all 3 roles for division 1 but only 1 role assigned to division 2 (which is only active in MayCo). Yet, I get the same assigned roles listed whether I put division 1,2 or 3 for company 1 (Gap). How do I factor in RoleDivisionTbl so that only assigned roles for a given division show up, otherwise show a Null assignment for every user in the company.

ie
If Company=1 and (Division=1 or 2 or 3)
Current Output:
UserID|UserName|RoleID|RoleName
1|Jim|1|Admin
2|Frank|3|Guest
3|Kelly|2|User
4|Al|Null|Null
5|James|Null|Null


If Company=1 and (Division=2 or Division=3)
Desired Output:
UserID|UserName|RoleID|RoleName
1|Jim|Null|Null
2|Frank|Null|Null
3|Kelly|Null|Null
4|Al|Null|Null
5|James|Null|Null

Thanks again!!!
boybles
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-26 : 19:29:31
Please post some PK FK information too.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-26 : 19:40:16
[code]SELECT u.UserID,
u.UserName,
d.RoleID,
d.RoleName
FROM @CompanyUserTbl AS cu
INNER JOIN @UserTbl AS u ON u.UserID = cu.UserID
LEFT JOIN (
SELECT DISTINCT ur.UserID,
r.RoleID,
r.RoleName
FROM @CompanyDivisionTbl as cd
INNER JOIN @RoleDivisionTbl AS rd ON rd.DivisionID = cd.DivisionID
INNER JOIN @UserRoleTbl AS ur ON ur.RoleID = rd.RoleID
INNER JOIN @RoleTbl AS r ON r.RoleID = ur.RoleID
WHERE cd.CompanyID = 1
AND cd.DivisionID IN (2, 3)
) AS d ON d.UserID = u.UserID
WHERE cu.CompanyID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

boybles
Starting Member

23 Posts
Posted - 2007-07-26 : 20:20:27
Peso,
That is brilliant. There's no way I could come up with that on my own at my level. As somebody who is just starting to learn joins, what advice do you have in putting together all these pieces? Do you have a systematic way of examining? Again...thank you for your help here. You have helped me in developing my software more than you know.
boybles
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site