Select Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Statement Help

Author

Topic

afrausto
Starting Member

1 Post

Posted - 2006-05-09 : 19:06:29

I'm having trouble developing the query to get the data from two tables. Sounds simple, but it's driving me nuts.

Table 1: tblProjects

PID ProjectName ApplicationComplete YearApplied
1   Project 1   True                2006
2   Project 2   True                2006
3   Project 3   False               2005
4   Project 4   True                2006
5   Project 5   True                2004
6   Project 6   True                2006

Table 2: tblScores

ScoreID PID JudgeType   Score
1       1   Chairperson 5
2       2   Chairperson 7
3       2   Co-Chair    6
4       4   Chairperson 9
5       5   Chairperson 10

From the two tables I want to pull all the records from tblProjects that meet the following criteria:

ApplicationComplete = True AND
YearApplied = '2006'

As well, I would like to append the scores from tblScores where tblProjects.PID = tblScores.PID AND JudgeType = 'Chairperson'

The results should be:

ProjectName Score
Project 1   5
Project 2   7
Project 4   9
Project 6

Although, Project 6 has not been given a score by a judge I still want it to appear in my results.

I've tried Left and Right Join based on PID relationship between the two tables but the results never shows records for Projects that have not been giving a score by a Chairperson.

Any assistance with this is greatly appreciated. Cheers!

afrausto

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-09 : 19:47:56

How about this:

SELECT tblProjects.ProjectName, tblScores.Score
FROM tblProjects LEFT JOIN tblScores
ON tblProjects.pid = tblScores.pid AND tblScores.JudgeType = 'Chairperson'
WHERE tblProjects.ApplicationComplete = 'True'
AND tblProjects.YearApplied = '2006'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources