Event Rooms
This View shows what Rooms are associated with each Event record.
Difficulty: Advanced
Purpose: Creates a field with all associated rooms, separated by commas.
Page: Events
Field List
Events.[Event_Start_Date] AS [Event Start Date]
, Events.[Event_Title] AS [Event Title]
, STUFF((SELECT ', ' + R.Room_Name FROM Rooms R JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID
WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 FOR XML PATH('')), 1, 2, '') AS [Event Rooms]View Clause
Events.[Event_Start_Date] >= GETDATE()Filters for Events with Rooms
To only list Events that have rooms associated with them you can add this criteria:
AND EXISTS (SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0)Filters for Events with Unapproved Rooms
To only list Events that have Unapproved Room Reservations, add this criteria:
AND EXISTS (SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 AND ISNULL(ER._Approved,0)=0 )Filter for Approved Events
To only list Events that have been approved, add this criteria:
AND ISNULL(Events._Approved,0)=1Filtered for Unapproved Room List
To return a list of Rooms that are not approved, you can alter the Event Rooms field from the base example.
, STUFF((SELECT ', ' + R.Room_Name FROM Rooms R JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 AND ISNULL(ER._Approved,0)=0 FOR XML PATH('')), 1, 2, '') AS [Unapproved Rooms]Techniques
- STUFF SQL Function
- EXISTS SQL Function
- ISNULL SQL Function
- GETDATE SQL Function