Solved

SQL Query Help

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

813 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

17 Experts available now in Live!

Get 1:1 Help Now