EXISTS ( subquery )
In the following example, a View on Contacts with this filter will only return Contacts that are registered in an active Event:
EXISTS(SELECT 1 FROM Event_Participants EP JOIN Events E ON E.Event_ID = EP.Event_ID WHERE EP.Participant_ID = Contacts.Participant_Record AND GETDATE() BETWEEN E.Event_Start_Date AND ISNULL(E.Event_End_Date,GETDATE()+1))