We need to have this query: All contacts not applied to selected event
SELECT c.* FROM CONTACTS c
LEFT JOIN EVENT_CONTACTS ec
ON c.ID = ec.ID_contact AND ec.ID_event = [linked to table Events Object manager instances.ID]
WHERE ec.ID_contact IS NULL;
But don't know how to add WHERE ec.ID_contact IS NULL condition to JOIN in LCA query structure
This is already configured:
Hi Harald,
Maybe I am overlooking something, but I believe that the filter section will implement your "where" condition in your SQL query.
Hi Harald,
As Miguel pointed out, you might want to use the Filter operation you have at the end of the query.
If you want to remove those results with an empty EVENT_CONTACTS.ID_contact you can use the Not Regex condition and set as filter value a dot "."
For example:
Hi Sebastian,
it's the other way round:
We want to have those results WITH an empty EVENT_CONTACTS.ID:
select * from CONTACTS
where id NOT in (select ID_contact from EVENT_CONTACTS
where ID_event =
[link to table Events Object manager instances.ID])
Hi Harald,
The filter above will remove those entries that have a match between the CONTACTS and the EVENT_CONTACTS, meaning that the result set should be the CONTACTS without an EVENT_CONTACT.
Referencing the image in my previous response, the Filter has to be done over the column of the EVENT_CONTACT.ID_Contact, instead of the "Organization".
Hi Sebastian,
got it. But unfortunately for Filter method I have only two options "equals" and "not equals". The "Not regex" isn't available in drop down.
In that case, it means that the column you are using for filtering is not a string, and that an extra step has to be made.
Before the new Filter operation, add a "Column Manipulation" operation to create a copy of your existing ID column. Then this new column should have the Not Regex option.
I have updated the image of the answer above that illustrates the new step.
Do you have an example of what the dataset looks like without the filter on ID_Event?