Stumped on a SQL Query

Question

Stumped on a SQL Query

Steve E.

SSC Enthusiast

Points: 128
More actions
April 24, 2012 at 2:32 pm

#259882

Greetings,
With full disclosure, I am not a SQL expert by any stretch of the imagination. I am a guy that can do basic queries, inserts, updates, etc.
I am trying to build a query based on my employee table that has all employees listed. In that same table, is a field for the employee's supervisor. What I am trying to capture is all employees that are associated with the supervisor and extract the supervisor's email. It is then my intent to create a SSIS job to email the supervisor 30 days prior to when an employee's review is due.
So for an example, here is the table data I have to work with:
EMPIDSUPIDLNFNEMAILEMP HireDate
10001500 Sparrow Jack Jack.Sparrow@abc.com1990-04-01 00:00:00.000
10011500 Teach EdwardEdward.Teach@abc.com1991-07-01 00:00:00.000
10021500 Roberts Bart Bart.Roberts@abc.com1991-09-16 00:00:00.000
10031500 Kidd William William.Kidd@abc.com2003-06-02 00:00:00.000
10041500 Morgan Henry Henry.Morgan@abc.com2010-03-01 00:00:00.000
1500 2000 Aubrey JackJack.Aubrey@abc.com2008-12-16 00:00:00.000
2000AveryHenryHenry.Avery@abc.com2001-11-20 00:00:00.000
Ideally, I would want to be able to have a result set that lists all employee's that are under Jack Aubrey's supervision with Jack's email as the Supervisor's email. I think my result set looks something like this:
EMPIDSUPIDLNFNSUP EMAILEMP HIRE DATE
10001500 Sparrow Jack Jack.Aubrey@abc.com1990-04-01 00:00:00.000
10011500 Teach EdwardJack.Aubrey@abc.com1991-07-01 00:00:00.000
10021500 Roberts Bart Jack.Aubrey@abc.com1991-09-16 00:00:00.000
10031500 Kidd William Jack.Aubrey@abc.com2003-06-02 00:00:00.000
1004 1500 Morgan Henry J Jack.Aubrey@abc.com2010-03-01 00:00:00.000
15002000AubreyJackHenry.Avery@abc.com2001-11-20 00:00:00.000
I am thinking that I may need to create a "supervisors" table and populate it with all the supervisors and their emails, and then join the tables, but in my limited SQL knowledge, I am guessing there is an easier way with a sub query or something like that. I have spent days trying to Google it, and figure it out for myself, but alas, I am reaching out. Any help that can eb suggested or provided would be great!
Thanks,
Steve E.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

nope a self join will work. with out create table statements and sample data its hard to give tested code but something like this may work

SELECT a.EmpID, a.BossID, a.EmpName, b.EmpEmail -- b.email is the bosses email

FROM employees a

Left Join Employees b

on a.EmpID = b.BossID

if you would like tested code you can take a look at the link in my signature for the way to create the DDL and Sample Data.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's an example of providing your data in a readily consumable format:

i took your post and ran it thru a few find-and-replace macros to get it like this:

With MySampleData ( EMPID,SUPID,LN,FN,EMAIL,EMP_HireDate)

AS

(

SELECT 1000,1500,'Sparrow','Jack','Jack.Sparrow@abc.com','1990-04-01 00:00:00.000' UNION ALL

SELECT 1001,1500,'Teach','Edward','Edward.Teach@abc.com','1991-07-01 00:00:00.000' UNION ALL

SELECT 1002,1500,'Roberts','Bart','Bart.Roberts@abc.com','1991-09-16 00:00:00.000' UNION ALL

SELECT 1003,1500,'Kidd','William',' William.Kidd@abc.com','2003-06-02 00:00:00.000' UNION ALL

SELECT 1004,1500,'Morgan','Henry',' Henry.Morgan@abc.com','2010-03-01 00:00:00.000' UNION ALL

SELECT 1500,2000,'Aubrey','Jack','Jack.Aubrey@abc.com','2008-12-16 00:00:00.000' UNION ALL

SELECT 2000,NULL ,'Avery','Henry','Henry.Avery@abc.com',NULL

)

SELECT * FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 3

As Capn Hector said a self join will work. I think he has the join condition slightly wrong and mixed up the empid and supid

WITH emp AS

(

SELECT 1000 AS empid, 1500 AS supid, 'Sparrow' AS ln, 'Jack' AS fn, 'Jack.Sparrow@abc.com' AS email, '1990-04-01' AS emphiredate

UNION ALL

SELECT 1001, 1500, 'Teach', 'Edward', 'Edward.Teach@abc.com', '1991-07-01'

UNION ALL

SELECT 1002, 1500, 'Roberts', 'Bart', 'Bart.Roberts@abc.com', '1991-09-16'

UNION ALL

SELECT 1003, 1500, 'Kidd', 'William', 'William.Kidd@abc.com', '2003-06-02'

UNION ALL

SELECT 1004, 1500, 'Morgan', 'Henry', 'Henry.Morgan@abc.com', '2010-03-01'

UNION ALL

SELECT 1500, 2000, 'Aubrey', 'Jack', 'Jack.Aubrey@abc.com', '2008-12-16'

UNION ALL

SELECT 2000, NULL, 'Avery','Henry', 'Henry.Avery@abc.com', '2001-11-20'

)

SELECT a.empid, a.supid, a.ln, a.fn, b.email, a.emphiredate

FROM emp a

Left outer Join emp b

on a.supid = b.empid

;

Dave

Steve E. SSC Enthusiast Points: 128 More actions · Answer 4

That's It! Thank you.

I was in the process of re-coding my example to repost and ask a clairification question to the first reply. But the Left Outer Join did it.

Thanks for the help. I really do appreciate it.

For what it is worth, next time I will post my example code correctly in order for easier assistance. Thanks for taking the time to "fix it" for me. Next time will be better....promise! 😀

Steve E.

CapnHector SSCoach Points: 16143 More actions · Answer 5

Dave Brooking (4/24/2012)
As Capn Hector said a self join will work. I think he has the join condition slightly wrong and mixed up the empid and supid
WITH emp AS
(
SELECT 1000 AS empid, 1500 AS supid, 'Sparrow' AS ln, 'Jack' AS fn, 'Jack.Sparrow@abc.com' AS email, '1990-04-01' AS emphiredate
UNION ALL
SELECT 1001, 1500, 'Teach', 'Edward', 'Edward.Teach@abc.com', '1991-07-01'
UNION ALL
SELECT 1002, 1500, 'Roberts', 'Bart', 'Bart.Roberts@abc.com', '1991-09-16'
UNION ALL
SELECT 1003, 1500, 'Kidd', 'William', 'William.Kidd@abc.com', '2003-06-02'
UNION ALL
SELECT 1004, 1500, 'Morgan', 'Henry', 'Henry.Morgan@abc.com', '2010-03-01'
UNION ALL
SELECT 1500, 2000, 'Aubrey', 'Jack', 'Jack.Aubrey@abc.com', '2008-12-16'
UNION ALL
SELECT 2000, NULL, 'Avery','Henry', 'Henry.Avery@abc.com', '2001-11-20'
)
SELECT a.empid, a.supid, a.ln, a.fn, b.email, a.emphiredate
FROM emp a
Left outer Join emp b
on a.supid = b.empid
;
Dave

ya i do have the join backwards. every day is a learning exercise.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]