This View shows what Rooms are associated with each Event record.

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()

Variations

Filter 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)

Filter 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)=1

Field 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