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
 New to SQL

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 grean
GB56 Dev blue
HH32 Vendor black
TH89 Vendor black
LK98 Dev blue
LK98 Media green

I need a SQL statement that runs against a user such as WHERE user = "LK98" and it would display;

UserID Department Team
------ ---------- ----
LK98 Dev blue
LK98 Media green

but 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.Team
from (
select department
from myTable
where userid = 'LK98'
) d
join myTable t
on t.department = d.department


OUTPUT:
userid department Team
------ ---------- -----
GB56 Dev blue
LK98 Dev blue
LK98 Media green


Be One with the Optimizer
TG
Go to Top of Page

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 Y
ORP\BAUH 555000 W Pe Den MBR Y
ORP\BAUH 555001 W Den C MBR Y
ORP\POE2 350200 W OPD Oto MBR Y
ORP\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 Y
Y 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_All
Jack_Reacher RATV 00 Required for Global
Jack_Reacher RATV 586000 W End
Jack_Reacher RATV 650001 W End
Jack_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.
Go to Top of Page

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>.profileid

If 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 Optimizer
TG
Go to Top of Page

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?
Go to Top of Page

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
) p
inner join UserProfile u
on u.profileid = p.profileid
INNER JOIN UserTeamAssign ut ON u.UserID = ut.UserorTeamID


EDIT:
added the group by in the derived table in case that statement returns duplicate profileids

Be One with the Optimizer
TG
Go to Top of Page

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'
)
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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.
Go to Top of Page

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 Optimizer
TG
Go to Top of Page
   

- Advertisement -