Solved

Help with select from other table

Posted on 2013-12-11
6
385 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

740 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