Quick question - 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

Quick question

Author

Topic

sqlnoobee
Starting Member

7 Posts

Posted - 2007-01-23 : 14:53:11

If I have:

Table Test
TestID (PK)
PretestTech (FK IndividualID)
PostTestTech (FK IndividualID)
ProjectManager (FK IndividualID)
TestInfo

Table Individuals
IndividualID (PK)
Initials
First
Last

Is there an efficient way to query for a list of tests with the PretestTech,PostTestTech,and Project manager initials? I guess I'm stuck because I want to Join the Test and Individual tables but there are three fields that need to be joined. So this is the only way I can think to make it work:

SELECT
TestID
,TestInfo
,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.PretestTech) AS 'PretestTech Initials'
,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.PostTestTech) AS 'PostTestTech Initials'
,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.ProjectManager) AS 'ProjectManager Initials'
FROM Test

Is this efficient or should I rethink my Table design?

Thanks

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2007-01-23 : 15:22:44

SELECT T.TestID,
T.TestInfo,
PreI.Initials as [PreTestTech Initials],
PostI.Initials as [PostTestTech Initials],
PMI.Initials as [ProjectManager Initials]
FROM Test AS T
INNER JOIN
Individuals AS PreI
ON (PreI.IndividualID = T.PreTestTech)
INNER JOIN
Individuals AS PostI
ON (PostI.IndividualID = T.PostTestTech)
INNER JOIN
Individuals AS PMI
ON (PMI.IndividualID = T.ProjectManager)

Jeff Banschbach, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 15:32:45

Replace INNER JOIN with LEFT JOIN.

Peter Larsson
Helsingborg, Sweden

sqlnoobee
Starting Member

7 Posts

Posted - 2007-01-23 : 15:59:06

Thanks Peter. That's awesome. I forgot about table aliases.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources