SQL WHERE NOT EXISTS STATEMENT

[Logger]

Can someone tell me why my query is not working the way I think/need it to.
I build a #tmp table with columns Company AccountNo, ContactName, Title,
etc in it.
There can be many records with the same company name, different contacts.
Example:
Company ABC 123 John Smith CEO
Company ABC 345 Mary Adams CFO
Company DEF 222 Tony Curtis

On my second #tmp table I need to pull out just one contact per companyName
.. Below is the query I’m using but it’s pulling all records.

--drop table #MainAccounts
CREATE TABLE #MainAccounts(
Company varchar(40)
,accountno varchar(20)
)
INSERT INTO #MainAccounts
SELECT
a.Company
,a.accountno
FROM #GetMains a
WHERE NOT EXISTS ( SELECT b.Company
FROM #MainAccounts b
Where b.Company=a.Company)
Help with suggestions!!

[John Bailo]

Why not use HAVING with the count = 1?

Logger wrote:

Can someone tell me why my query is not working the way I think/need it to.
I build a #tmp table with columns Company AccountNo, ContactName, Title,
etc in it.
There can be many records with the same company name, different contacts.
Example:
Company ABC 123 John Smith CEO
Company ABC 345 Mary Adams CFO
Company DEF 222 Tony Curtis

On my second #tmp table I need to pull out just one contact per companyName
. Below is the query I’m using but it’s pulling all records.

--drop table #MainAccounts
CREATE TABLE #MainAccounts(
Company varchar(40)
,accountno varchar(20)
)
INSERT INTO #MainAccounts
SELECT
a.Company
,a.accountno
FROM #GetMains a
WHERE NOT EXISTS ( SELECT b.Company
FROM #MainAccounts b
Where b.Company=a.Company)
Help with suggestions!!