ETL triple reference timeKey, subquery returned more than 1 value

Question

I am writing an ETL and I am trying to make my dimension reference the time dimension three times. But I can't even get the first one right.

The error says:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My code:

SELECT e.eventName, 
       e.eventType, 
       e.numberOfPersons,
      (SELECT  timeKey 
       FROM    starSchema.dbo.timeDim 
       JOIN    reservation r 
              ON r.reservationDate = timeDim.DATE) AS resDate, 
       e.eventStartDate, 
       e.eventEndDate, 
       contact.name, 
       customer.company
FROM   events e 
JOIN   reservation r 
       ON e.reservationId = r.reservationId
JOIN   customer 
       ON e.customerId = customer.customerId
JOIN   contact 
       ON customer.contactId = contact.contactId

I am trying to join the source datetime with my time dimension and return timeKey.

My goal is to have a timeKey(int) to reference the time dimension.

I want to do this with r.reservationdate, e.eventStartDate and e.eventEndDate.

Picture of my time Dimension:

Picture of Source:

Answer 1

I think you just want a correlated subquery:

(SELECT timeKey FROM starSchema.dbo.timeDim WHERE r.reservationDate = timeDim.DATE) AS resDate, 

You don't want a JOIN in the subquery. You want the connection to be to the reservation table in the outer query.