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

SQL Server 2005 Forums

Transact-SQL (2005)

Joins

Author

Topic

hugh_mungo
Starting Member

10 Posts

Posted - 2007-03-20 : 05:29:02

Hi

I am trying to extract the following:

Project.reference
Person.Surname
Equipment.Reference

Where I have the following tables.

PROJECT
idproject
reference

PERSON
idperson
surname

EQUIPMENT
idequipment
reference

PROJLINK
idproject
linktable [contains 'PERSON' or 'EQUIPMENT']
linkid [contains idperson or idequipment]

Is this possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-20 : 05:38:16

try this


select p.reference,
       r.surname,
       e.reference
from   PROJECT p inner join PROJLINK l
       on  p.idproject = l.idproject
       left join PERSON r
       on  l.linktable = 'PERSON'
       and l.linkid    = r.idperson
       left join EQUIPMENT e
       on  l.linktable = 'EQUIPMENT'
       and l.linkid    = e.idequipment

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 05:41:56

Yes.

SELECT pt.reference, case when pl.linktable = 'person' then p.surname else e.reference end
from project as pt
inner join projlink as pk on pk.idproject = pt.idproject
left join person as p on p.idperson = pk.linkid
left join equipment as e on e.idequipment = pk.linkid

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 05:42:28

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources