Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Joining Table to Itself


Joining Table to Itself

Author
Message
markafisher92
markafisher92
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 57
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??
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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. :-P

_______________________________________________________________

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)
markafisher92
markafisher92
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 57
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search