Reputation: 35
I am lost in joining 3 tables. I have 3 tables:
Now I want to create a timeline so that for every event I have a date range with for every day a count of reservations (or 0 if there are none). See the SQL Fiddle example
Now the issue is that I can select all records from date range and events but I cannot join the reservation table together with it to select the count of reservation on that day. I need it to create a graph of last x days for an event with the reservationcount on those days.
Expected output would be that for every date (2017-01-01,2017-01-02) I have a list of events(1,2,3..) and the reservationcount on that day (or if there is no matching reservationcount record, 0) How to do that?
2017-01-01 1 0
2017-01-02 1 65
2017-01-03 1 345
2017-01-04 1 0
2017-01-05 1 0
2017-01-06 1 0
Upvotes: 2
Views: 201
Reputation: 60513
You were close, but used the wrong outer join and missed a condition.
Select EventDates.SelDate,Events.EventID
,coalesce(Reservations.ResCount,0)
from EventDates cross join Events -- one row for each event/date
left Outer Join Reservations -- join existing reservations
on Events.EventID=Reservations.EventID
and EventDates.SelDate = Reservations.EventDate
order by Events.EventID, EventDates.SelDate
See fiddle
Upvotes: 0
Reputation: 17943
In your sample example there is no direct link between Events and EventDate, so if you want to attach all your events to every available date you need to have a Cartesian Product (cross join) between these tables.
There may or may not be a reservation on a specific date and event, so you need a LEFT JOIN with the reservation table.
Following query should give you the desired output.
SELECT SelDate,EV.EventId, ISNULL(ResCount,0) as ResCount FROM
EventDates ED
INNER JOIN Events EV ON 1=1
LEFT JOIN Reservations RES ON ED.SelDate = RES.EventDate AND EV.EventId=RES.EventId
Or you can also write it as
SELECT ED.*, ISNULL(ResCount,0) as ResCount FROM
(
SELECT SelDate, EventId
FROM EventDates ,Events
) ED
LEFT JOIN Reservations RES ON ED.SelDate = RES.EventDate AND ED.EventId=RES.EventId
Upvotes: 1