Tycoon Talk
Become a Big fish!
The number 1 forum for online business!
Post topics, ask questions, share your knowledge.
Tycoon Talk is part of Freelancer.com - find skilled workers online at a fraction of the cost.

The Database Forum


You are currently viewing our The Database Forum as a guest. Please register to participate.
Login



Reply
Old 10-26-2006, 10:10 PM SQL question
Novice Talker

Posts: 7
Trades: 0
I have written following query for a stored procedure:
SELECT
M.ProgramCode,
M.MeetingCode,


CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'

END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVP,
COUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendee


FROM
Programs P
INNER
JOIN eCDRReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
and MA.AttendeeType = 1
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
and MA.AttendeeType = 1
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @SubCompanyCode AND
P.ProgramCode = @ProgramCode AND
(MA.status = 2 OR MA.status = 4)

this query returns following values:
ProgramCode, MeetingCode, AttendeeType, NoofRSVP, NoofAttendee

In this query I have used MA.AttendeeType = 1 condition in my joins, 1 represent 'Participant' in the table, I am counting only 'Participants' here.

But it wont return any values if there is no Participant for some meeting, means Meeting is present in Programs and eCDRReservations table but there is no Attendeetype=1 in MeetingAttendees table for that meeting.

My task is, I have to modify the query in such a way so that it return 0 as NoofRSVP and 0 as NoofAttendee for all the meetings where there is no Participant. I have to keep all the where clauses too.
neha101 is offline
Reply With Quote
View Public Profile
 
 
Register now for full access!
Old 11-03-2006, 04:09 PM Re: SQL question
Extreme Talker

Posts: 246
Trades: 3
I don't want to pretend to be an expert, and I also don't want to pretend that I fully understand the entire query and the existing data, however, the fact that you're doing a left outer join on MeetingAttendees probably is the reason why you're not getting rows back where there are zero participants. I would try doing a different join (full outer?) until I received the data needed, then add more conditions to the where clause until I get exactly what I want.
__________________

Please login or register to view this content. Registration is FREE
|
Please login or register to view this content. Registration is FREE
CouponGuy is offline
Reply With Quote
View Public Profile
 
Reply     « Reply to SQL question
 

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off





   
RSS Feed  Feeds: RSS   JS   XML
RSS Feed  Feeds for this forum: RSS   JS   XML



Page generated in 0.45929 seconds with 12 queries