Solved

Help with select from other table

Posted on 2013-12-11
6
380 Views
Last Modified: 2013-12-11
I need to insert info into a table from another table but I need the info to be filtered from the current table:

here is my example:

Table 1 (Employees)
----------
ID       first_name   last_name
---       ---------------   --------------
1          John              Smith
2          Jane               Doe
3         Johnny           Walker
4         Bruce             Wayne

Table 2 (Details)

ID      Title         Supervisor_ID
----     -------         -------------------
2         Clerk                 1
3         Puchaser          1
4         President         4


I want to make a  temp table  or select that gives me:


ID      first_name       last_name       Supervisor_Name
----     -------------          --------------       ------------------------


Thanks,


Lyo
0
Comment
Question by:tenchulyo
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:virtuadept
ID: 39711636
You are going to do

SELECT t1.ID, t1.first_name, t1.last_name,
   ts.first_name+" "+ts.last_name as Supervisor_Name
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.id = t2.id
INNER JOIN table1 as ts
ON t2.supervisor_id = ts.id
0
 
LVL 11

Accepted Solution

by:
Simone B earned 250 total points
ID: 39711637
This should give you what you're looking for.

SELECT Emp.ID, Emp.first_name, Emp.last_name, Sup.first_name + ' ' + Sup.last_name
FROM Table1 Emp
INNER JOIN Table2 ON Emp.ID = Table2.ID
LEFT OUTER JOIN Table1 Sup ON Table2.Supervisor_ID = Sup.ID

Open in new window

0
 
LVL 11

Expert Comment

by:Simone B
ID: 39711641
I used a LEFT JOIN to the supervisor table, so that you will still see John Smith in the results, but the Supervisor's Name for him will be NULL.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 8

Expert Comment

by:virtuadept
ID: 39711659
Good point Buttercup1. You could also use this:

SELECT DISTINCT t1.ID, t1.first_name, t1.last_name, 
   ISNULL(ts.first_name+' '+ts.last_name,t1.first_name+' '+t1.last_name) as Supervisor_Name
FROM table1 as t1
LEFT OUTER JOIN table2 as t2
ON t1.id = t2.id
LEFT OUTER JOIN table1 as ts
ON t2.supervisor_id = ts.id

Open in new window


if you want to have their own name instead of  Null on Supervisor name for employees that do not have a supervisor.
0
 

Author Comment

by:tenchulyo
ID: 39711845
Left outer join!! Exactly what I was looking for!! Thanks!!
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39711857
You're welcome!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question