?
Solved

Help with select from other table

Posted on 2013-12-11
6
Medium Priority
?
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

762 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