Displaying Zero Count Rows - 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

SQL Server Development (2000)

Displaying Zero Count Rows

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-03-24 : 15:17:22

I have the following query where I want to return zero count rows for totalPhaseHours. It's currently only returning rows with data:

Current Results:
totalPhaseHours
Planning 977 500 5/30/2007 1083 21 Open
Testing 2667.5 1288 5/30/2007 2628 24 Open
Documentation 93 81 5/30/2007 97 25 Open

Expected Results:
totalPhaseHours
Planning 977 500 5/30/2007 1083 21 Open
Analysis 0 397.5 5/30/2007 413 22 Open
Development 0 784 5/30/2007 1242 23 Open
Testing 2667.5 1288 5/30/2007 2628 24 Open
Documentation 93 81 5/30/2007 97 25 Open

SELECT P.phaseDesc, SUM(TC.timecardHours) AS totalPhaseHours, PD.originalBudget, PD.lastUpdate, PD.newBudget, PD.phaseDetailID, PD.phaseState
FROM TZIX_ETC_PHASES P LEFT OUTER JOIN
TZIX_ETC_TASKS T ON P.phaseID = T.phaseID LEFT OUTER JOIN
TZIX_ETC_PHASES_DETAIL PD ON PD.phaseID = P.phaseID AND PD.projectNumber = 4778 LEFT OUTER JOIN
TZIX_ETC_DATA TC ON TC.taskNumber LIKE T.taskNumberPrefix + '%'
WHERE (TC.projectNumber = 4778)
GROUP BY P.phaseDesc, PD.originalBudget, PD.newBudget, PD.lastUpdate, PD.phaseDetailID, P.phaseID, PD.phaseState
ORDER BY P.phaseID

I know I need an INNER JOIN select within the code, but I can't get the syntax down.

Thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-25 : 01:50:26

try this,

SELECT P.phaseDesc, SUM(TC.timecardHours) AS totalPhaseHours, PD.originalBudget, PD.lastUpdate, PD.newBudget, PD.phaseDetailID, PD.phaseState
FROM TZIX_ETC_PHASES P LEFT OUTER JOIN
TZIX_ETC_TASKS T ON P.phaseID = T.phaseID LEFT OUTER JOIN
TZIX_ETC_PHASES_DETAIL PD ON PD.phaseID = P.phaseID AND PD.projectNumber = 4778 LEFT OUTER JOIN
TZIX_ETC_DATA TC ON (TC.taskNumber LIKE T.taskNumberPrefix + '%'
AND(TC.projectNumber = 4778))
GROUP BY P.phaseDesc, PD.originalBudget, PD.newBudget, PD.lastUpdate, PD.phaseDetailID, P.phaseID, PD.phaseState
ORDER BY P.phaseID

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-03-26 : 10:40:34

PeterNeo: Thank you for the tweak to the code. It makes sense now. Your reply is appreciated.

quote:
Originally posted by PeterNeo

try this,

SELECT P.phaseDesc, SUM(TC.timecardHours) AS totalPhaseHours, PD.originalBudget, PD.lastUpdate, PD.newBudget, PD.phaseDetailID, PD.phaseState
FROM TZIX_ETC_PHASES P LEFT OUTER JOIN
TZIX_ETC_TASKS T ON P.phaseID = T.phaseID LEFT OUTER JOIN
TZIX_ETC_PHASES_DETAIL PD ON PD.phaseID = P.phaseID AND PD.projectNumber = 4778 LEFT OUTER JOIN
TZIX_ETC_DATA TC ON (TC.taskNumber LIKE T.taskNumberPrefix + '%'
AND(TC.projectNumber = 4778))
GROUP BY P.phaseDesc, PD.originalBudget, PD.newBudget, PD.lastUpdate, PD.phaseDetailID, P.phaseID, PD.phaseState
ORDER BY P.phaseID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources