?
Solved

Help with select from other table

Posted on 2013-12-11
6
Medium Priority
?
418 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 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
Technology Partners: 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!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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