The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Create query to show accounts with no contacts
Posted: 07 May 07 11:10 AM
|
fiogf49gjkf0d Hi all.
I want to create a query that displays accounts with no contacts. I tried doing:
Contactid does not contain data, but that resulted in nothing and I know of accounts with no contacts.
How can I get this to work?
Thank you in advance. |
|
|
|
Re: Create query to show accounts with no contacts
Posted: 07 May 07 12:41 PM
|
fiogf49gjkf0d These steps will allow you to create a group of all accounts with no contacts.
1) Create an account group named "No Contacts"
2) Add condition on ACCOUNTID
3) Select "IN" as the operator
4) Enter the following in the "Value" box: select accountid from contact
5) After saving the condition, right-click it and select "NOT".
This group should now return all accounts with no contacts. |
|
|
| |
|
Re: Create query to show accounts with no contacts
Posted: 07 May 07 4:52 PM
|
fiogf49gjkf0d There is an Integrity Checker entry called "Contacts with no Account (Delete)" that proceeds to delete any contacts not belonging to an account. The (simplified) SQL it uses is:
select * from contact where not exists (select accountid from account where contact.accountid = account.accountid)
This is identical to what Ryan posted except that contact and account tables are switched and where not IN is where not EXISTS. Where not IN doesn't parse in Admin's Execute SQL. The group I created following Ryan's method does return the same records I got from Execute SQL, though. Perhaps you don't have any Accounts with no contacts? They could be a contact with a valid contactid but with a First and Lastname of "" (showing up as blank). We've accumulated a lot of those over the years. |
|
|
| |
|