Solved

SQL Query Help

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now