OK, so I’m still getting to grips with this, but the help here is invaluable. This time I want to return a search for items using an OR criteria. I can return a simple or using “merged with” and that’s all good (i.e. status = Pending or status = Ready) . What I want to do though is execute my query against one table and return rows from another, joined table.
My data structure is an Event. I have a User that owns the Event, but that User can also be a participant in an other User’s Event. So, I want to return the Event’s owned by the User and also the Events that they are a participant in.
I have an Event thing and I have a column on there of EventParticipants, which is a Participant thing. On here I have a column of User which is of type User.
The search criteria I want is Events where Owner = Current User or Events where Current User is present in the EventParticipants (i.e. all the Events that the Current User has an interest in).
I’m able to get the first part, and I know how to use merged with to bring the sets together, but I can’t work out how to get Events where EventParticipant’s User = Current User.
In database terms this is a union, so I’d be interested to also understand if it would be possible to create a union all. I don’t need it in this scenario, as a user won’t be a participant in their own even, but I can see cases where I may want to get a union all so I’d be interested to understand how to achieve this.
Can anyone help me understand?