Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Help

Posted on 2008-06-13
3
Medium Priority
?
244 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 1200 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 800 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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