Pull Vales from Other Tables in Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pull Vales from Other Tables in Select Statement

Author

Topic

bwol
Starting Member

19 Posts

Posted - 2008-07-03 : 16:25:35

I have a stored procedure that contains a very simple SELECT statement (shown below).

Two of the fields selected, EmployerID and SenderID, are the primary keys for two other tables (named Employers and Senders).

I would like to modify this SELECT statement so that instead of EmployerID and SenderID being returned, certain other values associated with those primary keys are returned.

For example, instead of an EmployerID of 155 being returned, I would like "Joe's Body Shop" returned, which the the EmployerName field associated with the EmployerID of 155 in the Employers table.

Similarly, instead of 119 being returned for the SenderID, I would like "Joe Smith" returned, which is the value in the SenderName field associated with the SenderID of 119 in the Senders table.

Here is the stored procedure. Any suggestions are much appreciated!

CREATE PROCEDURE dbo.ReportsSelect
AS
SELECT ReportID,EmployerID,SenderID,ReportType,ReportPeriod FROM Reports

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-03 : 16:29:54

You can do this via a join.

SELECT t2.ColumnN, t3.ColumnZ
FROM SomeTable t1
INNER JOIN SomeOtherTable t2
ON t1.ColumnA = t2.ColumnA
INNER JOIN SomeYetAnotherTable t3
ON t1.ColumnA = t3.ColumnA

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-03 : 16:35:28

oh sorry tara, i was a little too late

SELECT
r.ReportID,
r.EmployerID,
e.EmployerName,
r.SenderID,
s.SenderName,
r.ReportType,
r.ReportPeriod
FROM Reports r,
Employers e,
Senders s
where r.EmployerID = e.EmployerID
and r.SenderID = s.SenderID

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

bwol
Starting Member

19 Posts

Posted - 2008-07-03 : 16:47:25

Thanks so much for the rapid response! Your suggestion worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources