July 21, 2005 at 4:08 pm
can you guys tell me what's wrong with the following query?
i'm trying to get a list of all the records in the renderedemailstatustypes (resid) along with a count of how many records from the rendered emails have that id. if i right join renderedemailstatustypes i should always get the full record set from renderedemailstatustypes shouldnt i? with 0's (or nulls) filling in where nothing matches from the renderedEmails table?
select
resid.[name] as typename,
count(re.renderedemailstatusid)as numEmails
from
renderedemails as re
right join renderedemailstatustypes as resid on resid.renderedemailstatusid = re.renderedemailstatusid
where re.emailsessionid = 313
group by re.renderedemailstatusid , resid.[name]
July 21, 2005 at 4:49 pm
Without sample data, I cannot test, but It looks like you do not need re.renderedemailstatusid in your group by.
What results are you getting?
and what results would you like to get.
Post some sample data, and we can figure it out.
July 21, 2005 at 5:02 pm
i'm trying to get a list of how many records i have from renderedEmails in each category.
Ex.
QueuedToRender 43
QueuedToSend 95
Sent 120
If there is only 1 category that items fall into i would like it to return 0's for the other 2
Ex.
QueuedToRender 0
QueuedToSend 0
Sent 258
select * from renderedemailstatustypes
RenderedEmailStatusID----Name
1------------------------QueuedToRender
2------------------------QueuedToSend
3------------------------Sent
(3 row(s) affected)
________________________________________________________________________
select top 10 emailsessionID, renderedemailstatusid from renderedemails where emailsessionid = 313
emailsessionID---renderedemailstatusid
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
313---------------3
(10 row(s) affected)
thanks for the help
July 21, 2005 at 8:26 pm
Try:
SELECT resid.[name] as typename,
COUNT(re.renderedemailstatusid) AS numEmails
FROM renderedemailstatustypes as resid
LEFT JOIN renderedemails AS re
ON resid.renderedemailstatusid = re.renderedemailstatusid
AND re.emailsessionid = 313
GROUP BY resid.[name]
Andy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply