Answers.Body: <p>Not quite what you asked, but Search Kit is based on APIv4 and you can also do it directly with APIv4:</p>
  <pre><code>$contacts = \Civi\Api4\Contact::get()
    -&gt;addSelect('id')
    -&gt;setJoin([
      ['Participant AS participant', FALSE, NULL,
      ['participant.contact_id', '=', 'id'], ['participant.event_id', '=', '1']]
    ])
    -&gt;setGroupBy(['id'])
    -&gt;addWhere('participant.id', 'IS NULL')
    -&gt;execute();
  </code></pre>
  <p>(see also <a href="https://civicrm.stackexchange.com/a/37837/225">https://civicrm.stackexchange.com/a/37837/225</a>)</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-06T13:30:36.890Z
Answers.LastActivityDate: 2020-10-06T13:30:36.890Z
Answers.UserDisplayName: Aidan
Answers.Body: <pre><code>SELECT cc.id, cp.id
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NULL;
  </code></pre>
  <p>You can confirm that this works by running this straightforward query to see the contacts who ARE registered for an event:</p>
  <pre><code>SELECT cc.id, cp.id 
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id 
  WHERE cp.event_id = 1;
  </code></pre>
  <p>Then confirm that this query is functionally the same:</p>
  <pre><code>SELECT cc.id, cp.id
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NOT NULL;
  </code></pre>
  <p>The first query is the same as the third, except uses <code>IS NULL</code> instead of <code>IS NOT NULL</code>.</p>
  <p>This search should be possible with the forthcoming Search Kit.  I tried a pre-release version but there was an error joining contacts to participants.  However, I was able to create the equivalent with contributions - &quot;All contacts without a member dues payment&quot;.  The screenshot below shows how you'll be able to do this with Search Kit very soon:</p>
  <p><a href="https://i.sstatic.net/mkjHF.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/mkjHF.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-20T16:50:31.180Z
Answers.Text: Jon I just want to say a huge thank you.  You put so much effort into this response.  I am so grateful.  Thank you!!!
Answers.UserDisplayName: richardsplayground
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-05T01:52:36.037Z
Answers.LastActivityDate: 2020-10-05T01:52:36.037Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>I need to search for all users who have not registered for an event.  <a href="https://civicrm.stackexchange.com/questions/4760/search-for-people-who-have-not-registered-for-an-event">Five years ago, I asked the question</a>, and the answer was to create a group of users who did register and then do an include/exclude search.</p>
  <p>The thing is that I need to do this on a regular basis, for many events.  Creating a group for every event became too cumbersome.</p>
  <p>I am trying to figure out how I can do this search, perhaps using the data processor extension.  Where I am stuck is figuring out how to express my intention: &quot;all users who have not registered&quot;.</p>
  <ul>
  <li><p>I tried using a search on Participants.  But if someone has not registered, they are not a participant.  So using a search like &quot;all participants for event X where status is not Registered&quot; only shows me people who did register but whose status is now Attended or Canceled for example. It does not show me people who have not registered.</p>
  </li>
  <li><p>The data processor extension allows you to join data, but I don't see how it lets you do the opposite of a join.  Essentially to say &quot;select all users&quot; plus &quot;exclude (reverse of join) any person who is a Participant for this event&quot;.</p>
  </li>
  </ul>
  <p>I believe this is where I am thinking about the problem wrong.  SQL doesn't have such an exclusion call, instead you are supposed to <a href="https://stackoverflow.com/questions/3420982/opposite-of-inner-join#:%7E:text=If%20you%20consider%20an%20inner,either%20table%20that%20don%27t.">re-think the question so that it becomes a query where join is possible</a>.</p>
  <p>So can anyone help me re-think this so that using &quot;join&quot; function of data processor is possible?</p>
  
Tags: <custom-search>
Title: Search for people who have NOT registered for an event using a custom search or data processor
ThreadText: # Search for people who have NOT registered for an event using a custom search or data processor
  
  (Asked by: username_0 on 2020-10-03)
  
  I need to search for all users who have not registered for an event. [Five years ago, I asked the question](https://civicrm.stackexchange.com/questions/4760/search-for-people-who-have-not-registered-for-an-event), and the answer was to create a group of users who did register and then do an include/exclude search.
  
  The thing is that I need to do this on a regular basis, for many events. Creating a group for every event became too cumbersome.
  
  I am trying to figure out how I can do this search, perhaps using the data processor extension. Where I am stuck is figuring out how to express my intention: "all users who have not registered".
  
  * I tried using a search on Participants. But if someone has not registered, they are not a participant. So using a search like "all participants for event X where status is not Registered" only shows me people who did register but whose status is now Attended or Canceled for example. It does not show me people who have not registered.
  * The data processor extension allows you to join data, but I don't see how it lets you do the opposite of a join. Essentially to say "select all users" plus "exclude (reverse of join) any person who is a Participant for this event".
  
  I believe this is where I am thinking about the problem wrong. SQL doesn't have such an exclusion call, instead you are supposed to [re-think the question so that it becomes a query where join is possible](https://stackoverflow.com/questions/3420982/opposite-of-inner-join#:%7E:text=If%20you%20consider%20an%20inner,either%20table%20that%20don%27t.).
  
  So can anyone help me re-think this so that using "join" function of data processor is possible?
  
  **Tags:** custom-search
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2020-10-05. Score: 3 | Accepted answer
  
  ```
  SELECT cc.id, cp.id
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NULL;
  
  ```
  
  You can confirm that this works by running this straightforward query to see the contacts who ARE registered for an event:
  
  ```
  SELECT cc.id, cp.id 
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id 
  WHERE cp.event_id = 1;
  
  ```
  
  Then confirm that this query is functionally the same:
  
  ```
  SELECT cc.id, cp.id
  FROM civicrm_contact cc 
  LEFT JOIN civicrm_participant cp ON cc.id = cp.contact_id AND cp.event_id = 1 WHERE cp.id IS NOT NULL;
  
  ```
  
  The first query is the same as the third, except uses `IS NULL` instead of `IS NOT NULL`.
  
  This search should be possible with the forthcoming Search Kit. I tried a pre-release version but there was an error joining contacts to participants. However, I was able to create the equivalent with contributions - "All contacts without a member dues payment". The screenshot below shows how you'll be able to do this with Search Kit very soon:
  
  [![enter image description here](https://i.sstatic.net/mkjHF.png)](https://i.sstatic.net/mkjHF.png)
  
  > **Comments:**
  > **username_0**: Jon I just want to say a huge thank you.  You put so much effort into this response.  I am so grateful.  Thank you!!!  
  
  ---
  
  ### Answer by username_1 on 2020-10-06. Score: 2
  
  Not quite what you asked, but Search Kit is based on APIv4 and you can also do it directly with APIv4:
  
  ```
  $contacts = \Civi\Api4\Contact::get()
    ->addSelect('id')
    ->setJoin([
      ['Participant AS participant', FALSE, NULL,
      ['participant.contact_id', '=', 'id'], ['participant.event_id', '=', '1']]
    ])
    ->setGroupBy(['id'])
    ->addWhere('participant.id', 'IS NULL')
    ->execute();
  
  ```
  
  (see also <https://civicrm.stackexchange.com/a/37837/225>)
  
  
  ---
  
  