I have a table of Events and table of EventMembers that hold an entry for each event/member pair (member can join more than one event). This EventMember table contains also the satus of the member at the event (paid, need to pay, free ticket). Now I want to get from the aggregae a list of all events with the status of a specific member in each of the evens or Null in case that member have no status for the give event.
If I do Join Event withorwithout EventMember and then filter EventMember.MemberId = MemberId, I get only those events in which the EventMember table has a record for that EVent/Member pair. But I want to have all events in the list. If I could filter the Member table such as Member.Id = MemberId before the Join it would have solved the issue.
Hi @Yaron Ben ,
That condition on member should not be a filter, but part of the WithOrWithout condition.
That condition is applied on the table being joined BEFORE the join is performed, a filter is applied AFTER the join on it's result.
Dorine
Thanks a lot. Of course it works :-)
Hi Yaron,
If I understand the question correctly, you should be able to use the Join condition directly to accomplish your goal. The join condition can be any boolean clause, so instead of adding a filter EventMember.MemberId = MemberId, you could include it directly in the join clause and not use a filter, for example:
Event Join WithOrWithout EventMember, clause:
Event.Id = EventMember.EventId AND EventMember.MemberId = MemberId
The result will have all Events in the list, and a null/empty match for those where the member doesn't have a corresponding EventMember entry.
Disclaimer: I'm not sure how performant this approach is, but you can implement it directly in an Aggregate. If you need to tailor/optimize the performance a bit more, then probably an Advanced SQL Query would be your best option.
Hopefully that helps!
The solution of @Dorine Boudry is correct. He got me right. I need a filtering that happen before the join. This can be done only by the conditions on the Source tab and not at the Filter tab. Anyway, thanks a lot for your intention to help ❤️
2 things :
Have a good weekend,