CODEWITHSUNDEEP
sqlsql-server
Fabian Tuender
Fabian Tuender

Reputation: 35

unable to join 3 tables in SQL

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

Answers (2)

dnoeth
dnoeth

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

PSK
PSK

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

Related Questions