little help with some tsql

Question

Given following table:

rowId  AccountId  Organization1  Organization2
-----------------------------------------------
1      1          20             10
2      1          10             20
3      1          40             30
4      2          15             10
5      2          20             15
6      2          10             20

How do I identify the records where Organization2 doesn't exist in Organization1 for a particular account

for instance, in the given data above my results will be a single record which will be AccountId 1 because row3 organization2 value 30 doesn't exist in organization1 for that particular account.

Answer 1

SELECT rowId, AccountId, Organization1, Organization2
FROM   yourTable yt
WHERE  NOT EXISTS (SELECT 1 FROM yourTable yt2 WHERE yt.AccountId = yt2.AccountId AND yt.Organization1 = yt2.Organization2)

Answer 2

There are two possible interpretations of your question. The first (where the Organization1 and Organization2 columns are not equal) is trivial:

SELECT AccountID FROM Table WHERE Organization1 <> Organization2

But I suspect you're asking the slightly more difficult interpretation (where Organization2 does not appear in ANY Organization1 value for the same account):

SELECT AccountID From Table T1 WHERE Organization2 NOT IN 
  (SELECT Organization1 FROM Table T2 WHERE T2.AccountID = T1.AccountID)