Joining Table to Itself

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Joining Table to Itself

Joining Table to Itself

Rate Topic

Display Mode

Topic Options

Author

Message

markafisher92

Posted Thursday, January 10, 2013 12:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:27 AM
Points: 2, Visits: 24

I need to return the firstname_vc, and lastname_vc from the ar.staffmaster table of the Supervisors. A staff member in this table may also be a supervisor. The birthcountry_vc is the staffcode_c of the staff person who is their supervisor. I need to show the first and last name of the supervisor in a report. How do I query the same table in order to do this? Like I said, the birthcountry_vc is the staffcode_c of the staff person who is their supervisor. I tried this, but I cant get the supervisors first and last name.
SELECT STM.staffcode_c as [Staff Code],
STM.firstname_vc as [First Name],
STM.lastname_vc as [Last Name],
PC.description_vc as [Program],
STM.department_vc as [Title]
STM.birthcountry_vc as [Spervisor]
FROM ar.staffmaster as STM
inner join ar.programcodes as PC on STM.division_vc = PC.code_c
inner join cd.rhd_incidents as I on I.staff_c = STM.birthcountry_vc or I.staff_c = STM.staffcode_c

It returns this;

Staff Code First Name Last Name Program Title Supervisor
frye Audra Frye THR NULL bcary
apoolson April Poolson TTp NULL NULL
bcary Bridget Cary OOP Couns NULL
mpardue Melissa Pardue FRD NULL NULL

As you can see, "bcary" is Audra Fryes' Supervisor, but "bcary is also a Staff member." How do I return bcarys first name for, should I join the table to itself??

Post #1405607

Sean Lange

Posted Thursday, January 10, 2013 1:22 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 8,634, Visits: 8,271

Hi and welcome to SSC!!! We need some details before we can offer much assistance here. We would like to see ddl (create table statements), sample data (insert statements) and desired output based on the sample data. You can find details about how to post this information and how to put it together by taking a look at the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1405624

Sean Lange

Posted Thursday, January 10, 2013 1:26 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 8,634, Visits: 8,271

This is likely VERY close.

SELECT STM.staffcode_c as [Staff Code],
STM.firstname_vc as [First Name],
STM.lastname_vc as [Last Name],
PC.description_vc as [Program],
STM.department_vc as [Title]
STM.birthcountry_vc as [Spervisor],
supervisor.firstname_vc as [Supervisor First Name],
supervisor.lastname_vc as [Supervisor Last Name]
FROM ar.staffmaster as STM
inner join ar.programcodes as PC on STM.division_vc = PC.code_c
inner join cd.rhd_incidents as I on I.staff_c = STM.birthcountry_vc or I.staff_c = STM.staffcode_c
left join ar.staffmaster supervisor on supervisor.staffcode_c = STM.[Whatever Field is the Supervisor staffcode_c]

I can't tell what column in staffmaster indicates the value of the supervisor. Your query says it is birthcountry_vc but somehow I doubt that is right. Tongue

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1405627

markafisher92

Posted Friday, January 11, 2013 11:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:27 AM
Points: 2, Visits: 24

Actually, I figured it out BigGrin

I just needed to add this to my SELECT statement;
SUP.firstname_vc as [Suprvisor First Name],
SUP.lastname_vc as [Suprvisor Last Name]

Since I was getting the name from the firstname_vc, lastname_vc of the same table. Remember, A staff person could be a Supervisor as well.
I then added this join;
inner join ar.staffmaster as SUP on STM.birthcountry_vc = SUP.staffcode_c
And it worked beautifully. Thanks for your reply though. I am a newbie.

Post #1406201

Sean Lange

Posted Friday, January 11, 2013 12:16 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 8,634, Visits: 8,271

No problem. I would suggest changing to a left join instead. If you use an inner join and there is no supervisor you won't get the employee information either.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1406220

« Prev Topic | Next Topic »

Permissions