problem in query

[milecimm]

Hi,
I'm binding a sqldtatasource to a gridview to obtain some calculations from the data. However something is wrong for the query because I know the calculations are wrong. I hope I can explain clearly:

I have data in database as follows:

I have a table with time slots (Slots):
SlotID Slot
=================
1 9-9.30
2 9.30-10
3 10-10.30
4 10.30-11

I have a table with events (Events):
ID Name SlotID ExtraSlotID Limit1 Limit2
===========================================
1 Event1 1 n/a 20 20
2 Event2 1 n/a 20 20
3 Event3 2 n/a 40 40
4 Event4 2 3 20 20
5 Event5 3 4 40 40
6 Event6 3 4 20 20
7 Event7 3 4 10 10

I'm querying my database to get the total of people attending events in each time slot:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

However, this is giving me wrong results (I need this table to give me sum of attendees for each slot as Main slot (PeopleLimit1) and as extra slot (PeopleLimit2) but it seems PeopleLimit2 is in a kind of loop and and sums twice when there are events in that slot but as main slot
SlotID PeopleLimit1 PeopleLimit2
================================
1 40 0
2 60 0
3 70 60 *wrong
4 0 70

If I use just main slot with this query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID
GROUP BY s.Slot, s.SlotID

I get:
SlotID PeopleLimit1
====================
1 40
2 60
3 70
4 0


If I use only the extra slot in my query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

I get this:
SlotID PeopleLimit2
1 0
2 0
3 20 *correct
4 70

The problem is how to put the last two tables together and sum PeopleLimit1 and PeopleLimit2.

Thanks for your help!

[Delerna]

I am sure you can get the first query working but you show two other queries
that are correct results and ask

how to put the last two tables together

so I thought I would show you that.
The answer is,
wrap the two queries as derived tables (subqueries)
and query from them

 
SELECT a.fieldsyouneed,b.fieldsyouneed
FROM
(   Your first query goes inbetween these brackets
) a
appropiate join
(   Your second query goes inbetween these brackets
) b
 

I will let you attempt to build this
Post back if you have problems