Using joins to return result set

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)

Using joins to return result set

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-12-15 : 04:32:03

Morning

I have three tables, LetterCode, LettersMailed, CycleInstance. I want to return the following results:


LetterCode	NumberMailed
1	        10
2	        2
3	        null
4	        31

Therefore, I want to return ALL the letterCodes including those that are null. However, I need to filter the count (numberMailed) using a filter in the 3rd table (CycleInstance). However, the 3rd table joins to LettersMailed. If I do an inner, left or right join on CycleInstance, I do not get all the letterCodes. For example:


select LetterCode, sum(NumberMailed) From LetterCode lc 
left join LettersMailed lm on lc.letterCodeID = lm.LetterCodeID
left join CycleInstance c ON C.CycleInstanceID = lm.CycleInstanceID
where CycleInstanceDate = '12 December 2005'
GROUP By LetterCode
order by LetterCode

I have worked around this using derived tables:


SELECT 	  T1.LetterCode
	, ISNULL(CONVERT(VARCHAR(15),T2.MailDate,106),'N/A')AS [Mail Date]
	, ISNULL(T2.NumberOfLetters,'0')AS [Number Mailed]
FROM
(SELECT DISTINCT   L.LetterCode
		 , L.LetterCodeID
FROM LetterCode L)AS T1
LEFT JOIN
(SELECT   SUM(NumberMailed)AS NumberOfLetters
	, MailDate
	, LetterCodeID 
FROM LettersMailed LM
INNER JOIN  CycleInstance C ON C.CycleInstanceID = LM.CycleInstanceID
WHERE C.CycleInstanceDate = @date
GROUP BY LetterCodeID, MailDate)AS T2
ON T1.LetterCodeID = T2.LetterCodeID
ORDER BY T1.LetterCode

Is there any way to rewrite the query using joins??!?!

Thanks in advance

Hearty head pats

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-18 : 21:10:08

can you provide some example data for your three tables?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources