Complex Joins - SQL Server Forums

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

Complex Joins

Author

Topic

Todzilla
Starting Member

8 Posts

Posted - 2014-01-06 : 15:53:14

Trying to understand complex joins (or complex to me, at least).

I have a series of tables which have data I want to join in order to get a useful report. Most of the joins work fine, e.g.:

SELECT DISTINCT *
From Documents INNER JOIN LookUpTable
ON LookUpTable.ObjectId = Documents.LastVersionOwnerId

This give me the name of the person who owns the most recent version of a document, as the LookUpTable table maps userid numbers to names.

But I also want to correlate the OriginalOwnerId column from the same Document table with its LookUpTable counterpart. I can't figure out how to get that second join, for the same tables, to work.

I'm sure this is simple for folks who truly understand the JOIN concept, but it's eluding me.

Thanks in Advance for any help.

Regards,

Todzilla
Western Hemisphere
SQL 2008 R2 Noob

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-06 : 16:14:49

I think you want this. It uses table aliases so you can join to the same table more than once.


select d.<columns>
       ,l1.name as LastestVersionOwner
       ,l2.name as OriginalVersionOwner
From Documents d
INNER JOIN LookUpTable l1
       ON l1.ObjectId = d.LastVersionOwnerId
inner join LookUpTable l2
       on l2.ObjectId = d.OriginalOwnerId

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources