SUM And JOIN And Possibly GROUP BY - Incorrect Value Returned By SUM

It's me again :)

So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:


CREATE PROCEDURE [dbo].[getAvailableTickets]
@eventId INT,
@standId INT,
@admissionDateId INT,
@concessionId INT,
@userId INT

AS

DECLARE @startyear DATETIME
DECLARE @endyear DATETIME
SELECT @startyear=CONVERT(datetime, '2006/01/01')
SELECT @endyear=CONVERT(datetime, '2006/12/31')


SELECT
[tblTickets].[id] AS ticketId,
[tblEvents].[id] AS eventId,
[tblStands].[id] AS standId,
[tblAdmissionDates].[id] AS admitDateId,
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities]. AS minBookingQuantity,
[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
MIN([tblQuotas].[quota]) AS Quota,
[B]SUM([tblBasket].[ticket_quantity]) AS History,
[tblTickets].[price],
[tblTickets].[availability]

FROM [tblTickets]
LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets].
LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id]
LEFT JOIN [tblQuotas]ON
([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
[B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]

WHERE 1=1
AND ([tblTickets].[ticket_open] = 1)
AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1))
AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1))
AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1))
AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
[B]AND ([tblOrders].[user_id] = @userId OR @userId=0)

GROUP BY
[tblTickets].[id],
[tblEvents].[id],
[tblStands].[id],
[tblAdmissionDates].[id],
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblTickets].[price],
[tblTickets].[availability]
GO



Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.

This is what's in tblBasket:


idticket_idquantityorder_iddate
152321/03/2006
262321/03/2006
3144421/03/2006
4154421/03/2006
551421/03/2006


Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".

When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?

And how do i get it to return all tickets regardless of whether you've bought them previously or not?