January 8, 2010 at 1:27 pm
I'm more than sure that this is a question that's been addressed in the past on this forum and possibly others, but I'm not even what to call this sort of thing that I need help with. Again, forgive my ignorance, I'm SO not a SQL ninja 😛
I have a table that houses information on employees:
Name, email, NT ID, Manager, e-mail, account type, etc..
I end up doing a very basic query to return all rows where account type is set to 'contractor' and the manager field for these users comes back with a group (ie Contractor Group 123). All of these contractor groups have entries in the same table, including manager information, which is what I need to drill down to.
Essentially, I need to write one query for finding every contractor in my table and then listing their manager not as the contractor group, but as the manager of the contractor group. I wish I could give you a more technical term for what this type of operation is called, but like I said, I don't know.
If anyone could point me to another forum thread that answers this question already and/or tell me what this sort of operation is called, I'd be much obliged and would be happy to get this stupid post off your board =)
Thank you!
Mitch
January 8, 2010 at 1:37 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2010 at 4:24 pm
Sorry about that, first time posting.
The table I'm querying looks like this (just a copy/paste of the create statement):
CREATE TABLE [dbo].[BasicAccountInfo](
[UserObjectSID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserSourceDomain] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserFullName] [varchar](125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUserID] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserEmpID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDN] [varchar](175) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserManagerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserCompany] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserAccountType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserAccountStatus] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDescriptionFromAD] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserCreationDateFromAD] [datetime] NULL,
[UserLastLoginFromAD] [datetime] NULL,
[NEPAccountFlag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstLogonFlag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InfoGoodAsOf] [datetime] NULL,
CONSTRAINT [PK_BasicAccountInfo] PRIMARY KEY CLUSTERED
(
[UserObjectSID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
If I do a basic query like this:
select top 1 UserFullName, UserManagerName
from basicaccountinfo
where useraccounttype = 'contractor'
I get this result set:
UserFullName
Cxxxxx, Joanne
UserManagerName
CN=Contractors 1009340,OU=Contractor Group Accounts,DC=xxx,DC=com
Instead of seeing that "Contractors 1009340" as the manager name, I want to see the manager name of the contractors group, as seen by the result of this query:
select UserFullName, UserManagerName
from basicaccountinfo
where userfullname = 'Contractors 1009340'
Result:
UserFullName
Contractors 1009340
UserManagerName
CN=Vise\, Jeff,OU=Users,OU=Axxx,DC=xxx,DC=com
So the desired query/result would look like this:
select UserFullName, UserManagerName
from basicaccountinfo
where useraccounttype = 'contractor'
UserFullName
Cxxxxx, Joanne
UserManagerName
CN=Vxxx\, Jeff,OU=Users,OU=Axxx,DC=xxx,DC=com
Obviously, this info is all coming out of AD, but I just need help with the SQL side of things. Again, sorry for the noobie question.
Thanks in advance,
Mitch
January 9, 2010 at 2:44 am
Did you read the article?
What I'm asking is sample data in a usable format, something I don't have to spend hours on to get into a SQL table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2010 at 3:14 am
Hi Mitch
Looks like your problem could be solved by using a recursive CTE.
If you set up sample table scripts and data insertion scripts as Gail suggests, you will have a few solutions in no time. Read the article in her link, it will show you how to do this. The little time this will take, will save you oodles later.
Cheers
ChrisM@home
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 9, 2010 at 4:39 am
Hey Mitch,
Here's some code to demonstrate one approach to the problem.
DECLARE @Data TABLE
(
primary_key INTEGER NOT NULL PRIMARY KEY,
account_type NVARCHAR(30) NOT NULL,
full_name NVARCHAR(125) NOT NULL,
manager_name NVARCHAR(125) NOT NULL
);
INSERT @Data (primary_key, account_type, full_name, manager_name)
VALUES (1, N'contractor', N'Cxxxxx, Joanne', N'CN=Contractors 1009340,OU=Contractor Group Accounts,DC=xxx,DC=com'),
(2, N'manager', N'Contractors 1009340', N'CN=Vise\, Jeff,OU=Users,OU=Axxx,DC=xxx,DC=com');
SELECT D.full_name,
M.manager_name
FROM @Data D
OUTER
APPLY (
SELECT *
FROM @Data D2
WHERE D2.full_name = SUBSTRING(D.manager_name, 4, CHARINDEX(N',', D.manager_name) - 4)
AND D2.account_type = N'manager'
) M
WHERE D.account_type = N'contractor';
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2010 at 3:43 pm
Hey Paul,
Thanks for the code. I, unfortunately, can't add an "Account Type" of manager as you've suggested, as that field in my db is imported from another source.
I am going to just post some sample data from scratch, as has been suggested:
--Create the basic table
CREATE TABLE Users
(
primary_key INT NOT_NULL PRIMARY KEY,
FullName VARCHAR(100) NOT_NULL,
AccountType VARCHAR(50) NOT_NULL,
Manager VARCAHR(100) NOT_NULL
);
--Insert some sample data
INSERT Users (primary_key, FullName, AccountType, Manager)
VALUES
(1, 'John Smith', 'Employee', 'Sally Jones'),
(2, 'Jake Ryan', 'Contractor', 'Contractor Group 1'),
(3, 'Contractor Group 1', 'Other', 'Tom Lumberg');
--A simple select statement for all contractors and their managers will only render me a result of a "Contractor Group"
SELECT FullName, Manager
FROM Users
WHERE AccountType = 'Contractor'
--Results:
-- FullName Manager
-- Jake Ryan Contractor Group 1
--I'd like to see a result set of:
-- FullName Manager
-- Jake Ryan Tom Lumberg
It's that second result set that I'm unsure how to produce in a single query. Does that make better sense with sample data? Sorry I messed that up a couple times =/
Thanks,
Mitch
January 11, 2010 at 1:13 am
Mitch,
The code I posted was to demonstrate technique - it was not intended as a complete solution.
To be fair, I think you could easily have found the solution for yourself by looking at it and understanding how it works. Nevertheless, here is a complete solution, based on your sample code. Please take the time to understand how it works so that you are able to refine it as required.
--Create the basic table
DECLARE @Users TABLE
(
primary_key INT NOT NULL PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
AccountType VARCHAR(50) NOT NULL,
Manager VARCHAR(100) NOT NULL
);
--Insert some sample data
INSERT @Users (primary_key, FullName, AccountType, Manager)
VALUES
(1, 'John Smith', 'Employee', 'Sally Jones'),
(2, 'Jake Ryan', 'Contractor', 'Contractor Group 1'),
(3, 'Contractor Group 1', 'Other', 'Tom Lumberg');
--A simple select statement for all contractors and their managers will only render me a result of a "Contractor Group"
SELECT FullName, Manager
FROM @Users
WHERE AccountType = 'Contractor'
--Results:
-- FullName Manager
-- Jake Ryan Contractor Group 1
--I'd like to see a result set of:
-- FullName Manager
-- Jake Ryan Tom Lumberg
SELECT U.FullName,
M.Manager
FROM @Users U
OUTER
APPLY (
SELECT *
FROM @Users U2
WHERE U2.FullName = U.Manager
AND U2.AccountType = 'Other'
) M
WHERE U.AccountType = 'Contractor';
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2010 at 1:38 am
Why don't use SELF JOIN in this case? Is it simple than outer apply?
SELECT U.FullName,U1.Manager
FROM
@Users U
join @Users U1 on U.Manager=U1.FullName and U1.AccountType = 'Other'
WHERE
U.AccountType = 'Contractor';
January 11, 2010 at 2:38 am
nguyennd (1/11/2010)
Why don't use SELF JOIN in this case? Is it simple than outer apply?
Why don't you try both and let us know what differences you find?
It is certainly possible to write the query in a number of ways - which was why I previously said that I was posting code to demonstrate one possible approach.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply