Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Joining Table to Itself Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 12:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 2:09 PM
Points: 3, Visits: 34
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
Posted Thursday, January 10, 2013 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 13,073, Visits: 11,912
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1405624
Posted Thursday, January 10, 2013 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 13,073, Visits: 11,912
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.


_______________________________________________________________

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1405627
Posted Friday, January 11, 2013 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 2:09 PM
Points: 3, Visits: 34
Actually, I figured it out 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
Posted Friday, January 11, 2013 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 13,073, Visits: 11,912
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse