Get SUM of two columns linked to same column in different table

Question

Hi

I have a table "Events" for users to add events with the following fields:
EventID
EventName
EventSlot (--> slotID)
EventSlotExtra (--> slotID) (optional)
EventLimit

And I have another table "Slots"
SlotID (int)
SlotTime (this is 9am-10am, 10am-11am, etc)

I need to query both tables so I can get a total of people attending events per SlotTime. I know how to do this when only EventSlot is selected but not when EventSlotExtra is also selected. Can you help?
Many thanks

Answer 1

try this

Select s.SlotTime, 
   Count(e.EventId) + Count(x.EventId) EventCount
From Slots s    
    Left Join Events e 
        On e.EventSlot = s.SlotId
    Left Join Events x
        On x.EventSlotExtra = x.SlotId 
Group By s.SlotTime

Answer 2

Hi! This is great, Thanks!!!

I've just modified the code as I was looking for sum of attendees and not number of events per slot but otherwise the code from Charles Bretagna helped me a lot! Thanks again!

SELECT
s.SlotTime, s.SlotID, ISNULL(SUM(x.EventLimit), 0) + ISNULL(SUM(e.EventLimit), 0) AS PeopleLimit
FROM
Slots AS s
LEFT OUTER JOIN Events AS e ON e.EventSlot = s.SlotID
LEFT OUTER JOIN Events AS x ON x.EventSlot1 = s.SlotID
GROUP BY s.SlotTime, s.SlotID
ORDER BY s.SlotID