January 10, 2013 at 12:46 pm
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 THRNULLbcary
apoolson April PoolsonTTpNULLNULL
bcary BridgetCaryOOPCouns NULL
mpardue MelissaPardueFRDNULLNULL
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??
January 10, 2013 at 1:22 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2013 at 1:26 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2013 at 11:14 am
Actually, I figured it out:-D 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.
January 11, 2013 at 12:16 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply