Answers.Body: <p>Yes you can! I fiddled with the APIv4 Explorer a bit and came up with this:</p>
  <p><a href="https://i.sstatic.net/Es68m.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Es68m.png" alt="APIv4 Explorer" /></a></p>
  <p>The trick is to join onto <code>RelationshipCache</code> with the relationship type(s) you are wanting to exclude (note that the double quotes around <code>'&quot;Parent of&quot;'</code> are needed because you are passing a string literal not the name of a field or other SQL expression). And then in the WHERE clause specify that you want the relationship_cache.id to be NULL.</p>
  <p>The <code>GroupBy</code> is necessary because your join can potentially produce duplicate results if the contact has more than one relationship, although technically that's impossible with this query so, meh, you could try leaving it off.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-07T18:15:05.623Z
Answers.Text: This is a good pattern for NOT IN (subquery).
Answers.UserDisplayName: Joe Murray
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-04T02:29:21.973Z
Answers.LastActivityDate: 2020-10-04T02:29:21.973Z
Answers.UserDisplayName: Coleman
Body: <p>How can I find contacts using the APIv4 that don't have any relationships of a specific type?</p>
  <p>I want the first n contacts ordered by a date field meeting various conditions excluding those with a relationship of type X.</p>
  <p>There are various ways it could be done with multiple calls or php looping but it would be nice to be able to do it in a single call.</p>
  <p>In SQL one option using a subquery is:</p>
  <pre><code>SELECT * FROM Contacts
  WHERE &lt;various things&gt;
  AND contact_id NOT IN (SELECT contact_id_a FROM Relationships WHERE &lt;conditions&gt;)
  ORDER BY &lt;something&gt;
  LIMIT &lt;n&gt;
  </code></pre>
  <p>Can APIv4 produce something like that?</p>
  
Tags: <api><apiv4>
Title: Finding contacts without relationships (API)
ThreadText: # Finding contacts without relationships (API)
  
  (Asked by: username_0 on 2020-10-02)
  
  How can I find contacts using the APIv4 that don't have any relationships of a specific type?
  
  I want the first n contacts ordered by a date field meeting various conditions excluding those with a relationship of type X.
  
  There are various ways it could be done with multiple calls or php looping but it would be nice to be able to do it in a single call.
  
  In SQL one option using a subquery is:
  
  ```
  SELECT * FROM Contacts
  WHERE <various things>
  AND contact_id NOT IN (SELECT contact_id_a FROM Relationships WHERE <conditions>)
  ORDER BY <something>
  LIMIT <n>
  
  ```
  
  Can APIv4 produce something like that?
  
  **Tags:** api, apiv4
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2020-10-04. Score: 4 | Accepted answer
  
  Yes you can! I fiddled with the APIv4 Explorer a bit and came up with this:
  
  [![APIv4 Explorer](https://i.sstatic.net/Es68m.png)](https://i.sstatic.net/Es68m.png)
  
  The trick is to join onto `RelationshipCache` with the relationship type(s) you are wanting to exclude (note that the double quotes around `'"Parent of"'` are needed because you are passing a string literal not the name of a field or other SQL expression). And then in the WHERE clause specify that you want the relationship\_cache.id to be NULL.
  
  The `GroupBy` is necessary because your join can potentially produce duplicate results if the contact has more than one relationship, although technically that's impossible with this query so, meh, you could try leaving it off.
  
  > **Comments:**
  > **username_2**: This is a good pattern for NOT IN (subquery).  
  
  ---
  
  