Answers.Body: <p>You can use this SQL if you have access to the database directly. Replace the "5" with the actual id of the relationship type you're interested in. If you don't know the id you can see it if you go to admin - customize - relationship types and hover over the view link then you'll see the id in the url in the browser status bar.</p>
  
  <p><code>select c.display_name from civicrm_contact c left join civicrm_relationship r on (r.contact_id_a=c.id AND r.relationship_type_id=5) where c.contact_type='Organization' and r.id IS NULL;</code></p>
  
  <p>Depending on which direction you've defined the relationship you may need to replace <code>contact_id_a</code> with <code>contact_id_b</code> above.</p>
  
  <p>I think there's a bug in the CiviReport - Contact Reports - Relationship Report otherwise you might be able to do it from there, setting contact type A to be Organization and relationship to be "Is Empty", but it doesn't seem to recognize the latter properly.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-08-20T21:29:02.940Z
Answers.LastActivityDate: 2019-08-20T21:29:02.940Z
Answers.UserDisplayName: Demerit
Body: <p>My organisation is primarily (but not solely) a network of other organisations, so on Civi we have lots of "organisation" contact types, and lots of individual contacts. Some of these individuals are representative or contact person of their organisation to <em>our</em> organisation. I use the relationship tool to connect individuals to organisations as respresentatives (I made a new relationship type), and that works really well.</p>
  
  <p>Some organisations haven't nominated someone to be a rep, or we haven't got them into CiviCRM properly yet (we're still slowly setting up). What I'm struggling to work out is how to run a search of organisations which don't yet have that relationship type with anyone. Is this possible anyhow?</p>
  
  <p>Thanks.</p>
  
Tags: <search><relationships><custom-search>
Title: Organisation relationship search
ThreadText: # Organisation relationship search
  
  (Asked by: username_0 on 2019-08-20)
  
  My organisation is primarily (but not solely) a network of other organisations, so on Civi we have lots of "organisation" contact types, and lots of individual contacts. Some of these individuals are representative or contact person of their organisation to *our* organisation. I use the relationship tool to connect individuals to organisations as respresentatives (I made a new relationship type), and that works really well.
  
  Some organisations haven't nominated someone to be a rep, or we haven't got them into CiviCRM properly yet (we're still slowly setting up). What I'm struggling to work out is how to run a search of organisations which don't yet have that relationship type with anyone. Is this possible anyhow?
  
  Thanks.
  
  **Tags:** search, relationships, custom-search
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2019-08-20. Score: 2
  
  You can use this SQL if you have access to the database directly. Replace the "5" with the actual id of the relationship type you're interested in. If you don't know the id you can see it if you go to admin - customize - relationship types and hover over the view link then you'll see the id in the url in the browser status bar.
  
  `select c.display_name from civicrm_contact c left join civicrm_relationship r on (r.contact_id_a=c.id AND r.relationship_type_id=5) where c.contact_type='Organization' and r.id IS NULL;`
  
  Depending on which direction you've defined the relationship you may need to replace `contact_id_a` with `contact_id_b` above.
  
  I think there's a bug in the CiviReport - Contact Reports - Relationship Report otherwise you might be able to do it from there, setting contact type A to be Organization and relationship to be "Is Empty", but it doesn't seem to recognize the latter properly.
  
  
  ---
  
  