Solved

SQL Query Help

Posted on 2008-06-13
3
227 Views
Last Modified: 2010-03-20
Hi,

I have two tables:

People:
PeopleID
Name
E-Mail

Projects:
ProjectID
LeaderID
Participant1ID
Participant2ID
Participant3ID
Participant4ID

All of the fields in the Projects table, except ProjectID, are foreign keys to the PeopleID in the People table. I need to construct a query that will pull the PeopleID, Name, and E-Mail of the Leader and all four Participants, based on a Project ID. Something like this:

SELECT {fields}
FROM People, Projects
WHERE ProjectID = 1234

It's possible (and likely) that some of the ParticipantIDs will be NULL.

What's the best way to construct this query?

I'm using SQL Server 2005, if that makes any difference to the query.

Thanks.
0
Comment
Question by:CMES-IT
3 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 300 total points
ID: 21783167
Join the People table to the Projects table 5 times on each of the foreign key fields using outer joins

OM Gang
SELECT ProjectID, LeaderID, People.PeopleName AS LeaderName, People.PeopleEmail AS LeaderEmail, Participant1ID, People_1.PeopleName AS Partic1Name, People_1.PeopleEmail AS Partic1Email, Participant2ID, People_2.PeopleName AS Partic2Name, People_2.PeopleEmail AS Partic2Email, Participant3ID, People_3.PeopleName AS Partic3Name, People_3.PeopleEmail AS Partic3Email, Participant4ID, People_4.PeopleName AS Partic4Name, People_4.PeopleEmail AS Partic4Email
FROM People AS People_4 RIGHT JOIN (People AS People_3 RIGHT JOIN (People AS People_2 RIGHT JOIN (People AS People_1 RIGHT JOIN (People RIGHT JOIN Projects ON People.PeopleID = Projects.LeaderID) ON People_1.PeopleID = Projects.Participant1ID) ON People_2.PeopleID = Projects.Participant2ID) ON People_3.PeopleID = Projects.Participant3ID) ON People_4.PeopleID = Projects.Participant4ID;

Open in new window

0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
ID: 21789382
Hi, you could try this :

select projectID,
LeaderID,pm.Name,pm.EMail,
Participant1ID,p1.Name,
Participant2ID,p2.Name,
Participant3ID,p3.Name,
Participant4ID,p4.Name

from Projects
inner join people pm on LeaderID = pm.PeopleID
left outer join people p1 on Participant1ID = p1.PeopleID
left outer join people p2 on Participant2ID = p2.PeopleID
left outer join people p3 on Participant3ID = p3.PeopleID
left outer join people p4 on Participant4ID = p4.PeopleID
0
 

Author Comment

by:CMES-IT
ID: 21794852
Thanks guys.

To be honest, I was expecting some sort of difference between these two... but I booted up SQL Server Profiler, and it's showing the same exact number of Reads and the same duration for both queries. The query will be run a lot, so it was important that I made sure I had the best performing query, and I was worried about joining five tables... turns out, it doesn't matter much in this instance. I'm going to give omgang more points for responding right away, but you deserve some points too, mark. Thanks a bunch!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

713 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