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.
Author |
Topic |
jude77
Starting Member
5 Posts |
Posted - 2013-10-30 : 09:56:49
|
I am very new to SQL, I am learning. I need some help with a SQL query. Lets say I have data that looks like so;UserID Department Team------ ---------- ----ST44 Media greanGB56 Dev blueHH32 Vendor blackTH89 Vendor blackLK98 Dev blueLK98 Media greenI need a SQL statement that runs against a user such as WHERE user = "LK98" and it would display;UserID Department Team------ ---------- ----LK98 Dev blueLK98 Media greenbut I also want to see who else is in that department. So for an example, I search for User1, it will show me that User1 is in A, B and C departments and also show me that User2 is in departments A and B and User3 is in department C. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 10:02:15
|
so you want to see all users that are in the same departments as LK98?Here's one way:;with myTable (UserID, Department, Team)as( select 'GB56', 'Dev', 'blue' union all select 'HH32', 'Vendor', 'black' union all select 'TH89', 'Vendor', 'black' union all select 'LK98', 'Dev', 'blue' union all select 'LK98', 'Media', 'green')select t.userid, t.department, t.Teamfrom ( select department from myTable where userid = 'LK98' ) djoin myTable t on t.department = d.departmentOUTPUT:userid department Team------ ---------- -----GB56 Dev blueLK98 Dev blueLK98 Media green Be One with the OptimizerTG |
|
|
jude77
Starting Member
5 Posts |
Posted - 2013-10-30 : 10:54:32
|
Thanks that is a great help. My example could have been better. Let me layout what I have done and use the real thing maybe that will make more since for me. I have two tables one is "UserProfile" which looks like this.UserID ProfileID ProfileClass Inheritance--------------------------------------------------------------------ORP\BAUH 554014 W Institute Bio MBR YORP\BAUH 555000 W Pe Den MBR YORP\BAUH 555001 W Den C MBR YORP\POE2 350200 W OPD Oto MBR YORP\POE2 350200 W Pe Den MBR Y The other table is, "UserTeamAssign" and looks like this.IsATeam UserOrTeamID MemberOfTeamID TeamLeader--------------------------------------------------------------------Y AdminTeam N ORP\RATV Jack_Reacher YY BReview Y Lucas_Davinport N ORP\HAMI BReview Using the code I have so far I can pull what ProfileID a user is apart of but can not seem to also show who else is in those ProfileID's. Here is my code.SELECT MemberOfTeamID AS 'TeamName', SUBSTRING(UserID, CHARINDEX('\', UserID)+1, 250) AS 'UserID', Left(ProfileID, PatIndex('%[^0-9,.,-+-,^a-z]%', ProfileID)) AS 'Departments', SUBSTRING(ProfileID, PatIndex('%[^0-9,.,-+-]%', ProfileID), 8000) FROM UserProfile u INNER JOIN UserTeamAssign ut ON u.UserID = ut.UserorTeamID WHERE MemberOfTeamID LIKE '%Jack_Reacher%' Which returns this and does show what ProfileID's Jack is apart of.TeamName UserID Departments (No column name)-----------------------------------------------------------Jack_Reacher RATV 00 Access_AllJack_Reacher RATV 00 Required for GlobalJack_Reacher RATV 586000 W EndJack_Reacher RATV 650001 W EndJack_Reacher RATV Planners I would like to see who else has access to the ProfileID(deparments) that Jack has access to by searching with jack_reacher. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 11:11:34
|
You didn't do what I did which was:create a derived table that returns just the profileID(s) for your user.then JOIN to that derived table to your tables on the profileid = <derived table>.profileidIf you want the actual working statement then please post working DDL/DML for your sample data so I don't have to type out all that table structure and sample data (again).Be One with the OptimizerTG |
|
|
jude77
Starting Member
5 Posts |
Posted - 2013-10-30 : 11:36:24
|
Thanks so much for the help! What I posted above is real data do you need something else that would help? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 11:56:26
|
untested because you didn't post any working DDL/DML for me SELECT MemberOfTeamID AS 'TeamName' , SUBSTRING(UserID, CHARINDEX('\', UserID)+1, 250) AS 'UserID' , Left(ProfileID, PatIndex('%[^0-9,.,-+-,^a-z]%', ProfileID)) AS 'Departments' , SUBSTRING(ProfileID, PatIndex('%[^0-9,.,-+-]%', ProfileID), 8000)from ( select u.ProfileID FROM UserProfile u INNER JOIN UserTeamAssign ut ON u.UserID = ut.UserorTeamID WHERE MemberOfTeamID LIKE '%Jack_Reacher%' group by u.profileid ) pinner join UserProfile u on u.profileid = p.profileidINNER JOIN UserTeamAssign ut ON u.UserID = ut.UserorTeamID EDIT:added the group by in the derived table in case that statement returns duplicate profileidsBe One with the OptimizerTG |
|
|
jude77
Starting Member
5 Posts |
Posted - 2013-10-30 : 12:38:09
|
Does this help in troubleshooting?;with UserProfile (UserID, ProfileID, ProfileClass, Inheritance)as( select 'ORP\BAUH', '554014 W Institute Bi', 'MBR', 'Y' union all select 'ORP\BAUH', '555000 W Ped Den', 'MBR', 'Y' union all select 'ORP\BAUH', '555001 W Den CF', 'MBR', 'Y' union all select 'ORP\BAUH', '557001 W Mass Spe', 'MBR', 'Y' union all select 'ORP\VIEL', '557001 W Mass Spe', 'MBR', 'Y') with UserTeamAssign (IsATeam, UserOrTeamID, MemberOfTeamID, TeamLeader)as( select 'Y', 'Jack_Reacher', '', '' union all select 'N', 'CHMCCORP\BAUH', 'Jack_Reacher', 'Y' union all select 'Y', 'Jason_Born', '', '' union all select 'N', 'CHMCCORP\VIEL', 'Jason_Born', 'Y' union all select 'Y', 'Lucas_Davinport', '', '' union all select 'N', 'CHMCCORP\MEYA', 'Lucas_Davinport', 'Y') |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 14:39:37
|
quote: Originally posted by jude77 Does this help in troubleshooting?;with UserProfile (UserID, ProfileID, ProfileClass, Inheritance)as( select 'ORP\BAUH', '554014 W Institute Bi', 'MBR', 'Y' union all select 'ORP\BAUH', '555000 W Ped Den', 'MBR', 'Y' union all select 'ORP\BAUH', '555001 W Den CF', 'MBR', 'Y' union all select 'ORP\BAUH', '557001 W Mass Spe', 'MBR', 'Y' union all select 'ORP\VIEL', '557001 W Mass Spe', 'MBR', 'Y') with UserTeamAssign (IsATeam, UserOrTeamID, MemberOfTeamID, TeamLeader)as( select 'Y', 'Jack_Reacher', '', '' union all select 'N', 'CHMCCORP\BAUH', 'Jack_Reacher', 'Y' union all select 'Y', 'Jason_Born', '', '' union all select 'N', 'CHMCCORP\VIEL', 'Jason_Born', 'Y' union all select 'Y', 'Lucas_Davinport', '', '' union all select 'N', 'CHMCCORP\MEYA', 'Lucas_Davinport', 'Y')
No, it doesn't. This data (you just posted) is different from the first set of data you posted. For either case I can't see how the two tables are correlated. - Based on this data - what is the expected results? - please describe in words how you get these results?Be One with the OptimizerTG |
|
|
jude77
Starting Member
5 Posts |
Posted - 2013-10-30 : 15:21:29
|
Thanks for the help. I just had to change a few things in your example but other than that it worked. I learn backwards by deconstructing things so what you provided was a big help. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 15:25:47
|
Glad you got it. You're welcome.Be One with the OptimizerTG |
|
|
|
|
|
|
|