November 2, 2010 at 3:22 pm
Is this what you're looking for?
(Having issues posting code - it's in the attachment.) Code is now below.
-- sample data
DECLARE @EmployeeDetails TABLE (id int PRIMARY KEY CLUSTERED,
name varchar(50),
AccountBalance int);
DECLARE @Employees TABLE (id int PRIMARY KEY CLUSTERED,
supervisor int NULL,
CHECK (((id = 1 and supervisor IS NULL) OR
(id > 1 and supervisor IS NOT NULL)) AND
id <> supervisor));
INSERT INTO @EmployeeDetails VALUES (1, 'CEO', 0);
INSERT INTO @EmployeeDetails VALUES (2, 'Dept Mgr 1', 100);
INSERT INTO @EmployeeDetails VALUES (3, 'Dept Mgr 2', 115);
INSERT INTO @EmployeeDetails VALUES (4, 'Dept Mgr 3', 149);
INSERT INTO @EmployeeDetails VALUES (5, 'PM1', 149);
INSERT INTO @EmployeeDetails VALUES (6, 'PM2', 165);
INSERT INTO @EmployeeDetails VALUES (7, 'PM3', 125);
INSERT INTO @EmployeeDetails VALUES (8, 'PM4', 133);
INSERT INTO @EmployeeDetails VALUES (9, 'PGMR1', 147);
INSERT INTO @EmployeeDetails VALUES (10, 'PGMR2', 154);
INSERT INTO @EmployeeDetails VALUES (11, 'PGMR3', 155);
INSERT INTO @EmployeeDetails VALUES (12, 'PGMR4', 162);
INSERT INTO @EmployeeDetails VALUES (13, 'PGMR5', 105);
INSERT INTO @EmployeeDetails VALUES (14, 'PGMR6', 118);
INSERT INTO @EmployeeDetails VALUES (15, 'PGMR7', 97);
INSERT INTO @EmployeeDetails VALUES (16, 'PGMR8', 85);
INSERT INTO @Employees VALUES (1, NULL);
INSERT INTO @Employees VALUES (2, 1);
INSERT INTO @Employees VALUES (3, 1);
INSERT INTO @Employees VALUES (4, 1);
INSERT INTO @Employees VALUES (5, 2);
INSERT INTO @Employees VALUES (6, 2);
INSERT INTO @Employees VALUES (7, 3);
INSERT INTO @Employees VALUES (8, 3);
INSERT INTO @Employees VALUES (9, 5);
INSERT INTO @Employees VALUES (10, 5);
INSERT INTO @Employees VALUES (11, 6);
INSERT INTO @Employees VALUES (12, 6);
INSERT INTO @Employees VALUES (13, 7);
INSERT INTO @Employees VALUES (14, 7);
INSERT INTO @Employees VALUES (15, 8);
INSERT INTO @Employees VALUES (16, 8);
-- get info for ID 3
declare @id int;
set @id = 3;
WITH CTE AS
(
-- get info for specified id
SELECT id, supervisor
FROM @Employees e
WHERE id = @id
UNION ALL
-- get info for people whose supervisor is in the CTE
-- works recursively!
SELECT e.id, e.supervisor
FROM @Employees e
JOIN CTE c
ON e.supervisor = c.id
)
-- get the final information
SELECT SupervisorName = sed.name,
EmployeeName = ed.name,
ed.AccountBalance
FROM CTE
LEFT JOIN @EmployeeDetails sed
ON CTE.supervisor = sed.id
JOIN @EmployeeDetails ed
ON CTE.id = ed.id;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 3:26 pm
WayneS (11/2/2010)
Is this what you're looking for?(Having issues posting code - it's in the attachment.)
-- sample data
DECLARE @EmployeeDetails TABLE (id int PRIMARY KEY CLUSTERED,
name varchar(50),
AccountBalance int);
DECLARE @Employees TABLE (id int PRIMARY KEY CLUSTERED,
supervisor int NULL,
CHECK (((id = 1 and supervisor IS NULL) OR
(id > 1 and supervisor IS NOT NULL)) AND
id <> supervisor));
INSERT INTO @EmployeeDetails VALUES (1, 'CEO', 0);
INSERT INTO @EmployeeDetails VALUES (2, 'Dept Mgr 1', 100);
INSERT INTO @EmployeeDetails VALUES (3, 'Dept Mgr 2', 115);
INSERT INTO @EmployeeDetails VALUES (4, 'Dept Mgr 3', 149);
INSERT INTO @EmployeeDetails VALUES (5, 'PM1', 149);
INSERT INTO @EmployeeDetails VALUES (6, 'PM2', 165);
INSERT INTO @EmployeeDetails VALUES (7, 'PM3', 125);
INSERT INTO @EmployeeDetails VALUES (8, 'PM4', 133);
INSERT INTO @EmployeeDetails VALUES (9, 'PGMR1', 147);
INSERT INTO @EmployeeDetails VALUES (10, 'PGMR2', 154);
INSERT INTO @EmployeeDetails VALUES (11, 'PGMR3', 155);
INSERT INTO @EmployeeDetails VALUES (12, 'PGMR4', 162);
INSERT INTO @EmployeeDetails VALUES (13, 'PGMR5', 105);
INSERT INTO @EmployeeDetails VALUES (14, 'PGMR6', 118);
INSERT INTO @EmployeeDetails VALUES (15, 'PGMR7', 97);
INSERT INTO @EmployeeDetails VALUES (16, 'PGMR8', 85);
INSERT INTO @Employees VALUES (1, NULL);
INSERT INTO @Employees VALUES (2, 1);
INSERT INTO @Employees VALUES (3, 1);
INSERT INTO @Employees VALUES (4, 1);
INSERT INTO @Employees VALUES (5, 2);
INSERT INTO @Employees VALUES (6, 2);
INSERT INTO @Employees VALUES (7, 3);
INSERT INTO @Employees VALUES (8, 3);
INSERT INTO @Employees VALUES (9, 5);
INSERT INTO @Employees VALUES (10, 5);
INSERT INTO @Employees VALUES (11, 6);
INSERT INTO @Employees VALUES (12, 6);
INSERT INTO @Employees VALUES (13, 7);
INSERT INTO @Employees VALUES (14, 7);
INSERT INTO @Employees VALUES (15, 8);
INSERT INTO @Employees VALUES (16, 8);
-- get info for ID 3
declare @id int;
set @id = 3;
WITH CTE AS
(
-- get info for specified id
SELECT id, supervisor
FROM @Employees e
WHERE id = @id
UNION ALL
-- get info for people whose supervisor is in the CTE
-- works recursively!
SELECT e.id, e.supervisor
FROM @Employees e
JOIN CTE c
ON e.supervisor = c.id
)
-- get the final information
SELECT SupervisorName = sed.name,
EmployeeName = ed.name,
ed.AccountBalance
FROM CTE
LEFT JOIN @EmployeeDetails sed
ON CTE.supervisor = sed.id
JOIN @EmployeeDetails ed
ON CTE.id = ed.id;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2010 at 5:52 pm
Thanks Jason - I occasionally have problems posting code - seems to be from work. I'd love to figure it out!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 5:59 pm
Do you use websense at work?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2010 at 6:10 pm
CirquedeSQLeil (11/2/2010)
Do you use websense at work?
I don't... but Work uses websense at work. What do you know about this interaction between them?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 6:11 pm
CirquedeSQLeil (11/2/2010)
Do you use websense at work?
I know I do, and it occassionally screws up for me too. They're related, I take it?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 7:03 pm
Yes it is related. Websense will block on occassion code snippets as well as images. I am quite limited at work as to the sites I can visit, blogs I can visit and there is no chance of streaming video.
Even gravatars are blocked through websense (sometimes).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2010 at 7:49 pm
As far as code snippets go... is there anything particular that it's looking for? It's a royal PIA trying to post code sometimes. I've had times where I can't post code on one thread, but can on others. If there's anything particular, I might be able to circumvent this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 9:44 pm
WayneS (11/2/2010)
As far as code snippets go... is there anything particular that it's looking for? It's a royal PIA trying to post code sometimes. I've had times where I can't post code on one thread, but can on others. If there's anything particular, I might be able to circumvent this.
Nothing that I have noticed. I have had it block on a simple select * statement and others it works fine.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply