Solved

Help with select from other table

Posted on 2013-12-11
6
361 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
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
Comment Utility
Left outer join!! Exactly what I was looking for!! Thanks!!
0
 
LVL 11

Expert Comment

by:Simone B
Comment Utility
You're welcome!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now