SELECT where does not exist

[ross1228]

I have a table with accounts, id/name. And a customer vendor map table.
I'm trying to create a list of accounts that do not already exist in my customer list.

Accounts: 1,2,3,4,5,6,7,8
Customers: 2,3

If those are my accounts and customers, I want to return 1,4,5,6,7,8 to populate my dropdown.

I've tried 'JOIN account_map ON account_map.aid <> accounts.id'
But that returns many duplicates.

Any Suggestions?

 

[bborissov]

SELECT Accounts.*
FROM Account
LEFT JOIN account_map ON account_map.aid = accounts.id
WHERE account_map.aid IS NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[cLFlaVA]

try something like:

select *
  from accounts a
       left join customers c on a.customer_id = c.customer_id
 where c.customer_id is null

*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

how about this (I think account_map is your customer table?)

from accounts LEFT JOIN account_map 
on accounts.id = account_map.aid
where account_map.aid is null

If you take a left join and ask for only the rows without a match (where <some column> is null) , it will give the result you want.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

at least I'm not the only person waiting for the <. to go away ;-)

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[ross1228]

Thanks guys, I knew it was something easy I was just overlooking.