SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 New to SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jude77
Starting Member

5 Posts

Posted - 10/30/2013 :  09:56:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 10/30/2013 :  10:02:15  Show Profile  Reply with Quote
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

Edited by - TG on 10/30/2013 10:06:47
Go to Top of Page

jude77
Starting Member

5 Posts

Posted - 10/30/2013 :  10:54:32  Show Profile  Reply with Quote
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.

Edited by - jude77 on 10/30/2013 10:57:50
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 10/30/2013 :  11:11:34  Show Profile  Reply with Quote
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 - 10/30/2013 :  11:36:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 10/30/2013 :  11:56:26  Show Profile  Reply with Quote
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

Edited by - TG on 10/30/2013 11:58:11
Go to Top of Page

jude77
Starting Member

5 Posts

Posted - 10/30/2013 :  12:38:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 10/30/2013 :  14:39:37  Show Profile  Reply with Quote
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 - 10/30/2013 :  15:21:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 10/30/2013 :  15:25:47  Show Profile  Reply with Quote
Glad you got it. You're welcome.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000