Solved

SQL Query Help

Posted on 2008-06-13
3
226 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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