Hi all.

We have SLX LAN v7.2.1.

I want to be able to create a group that lists all accounts without any contacts.

Any ideas how I can do this? Can it be done via query builder?

Thank you in advance!

Add a condition for the Table Account on AccountID

Set Operator = In

Value = (select accountid from contact)

Use value as literal = Checked

Back on the conditions Tab Right Click the line just added and select "Not".

I can send you screenshots if this is not clear.

Best, Brian

Thanks for the reply Brian.

I did your suggestion but it did not show any results (I know for a fact there are accounts with no contacts). Below is a copy of the SQL taken from the query builder.

<p>SELECT A1.ACCOUNTID, A1.USERFIELD9, A1.ACCOUNT, A1.STATUS FROM ACCOUNT A1 INNER JOIN CONTACT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) WHERE NOT (A2.ACCOUNTID IN (select accountid from contact))
 

Hi,

Bryans thread is correct and works fine.

It looks like you have used the Accountid column from the Contact table instead of the Account. Your query should show A1.Accountid instead of A2.Accountid after the NOT(

Also, you have an inner join to the Contact table which will only return Accounts with Contacts (the complete opposite to what you want)

Regards, Adam

Thanks Adam. That was it, I was using the Accountid from the Contact table and not the ACCOUNTs table.

Thank you to both you and Bryan!