A little help with some tsql

Question

Given the 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 to identify records in which Organization2 does not exist in Organization1 for a specific account.

for example, in the data above, my results will be a single record, which will be AccountId 1, because the row3 organization2 value of 30 does not exist in organization1 for this 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 columns of Organization1 and Organization2 are not equal) is trivial:

 SELECT AccountID FROM Table WHERE Organization1 <> Organization2 

But I suspect you are specifying a slightly more complex 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) 

Answer 3

Here is how you could do it:

Test data:

 CREATE TABLE #T(rowid int, acc int, org1 int, org2 int) INSERT #T SELECT 1,1,10,10 UNION SELECT 2,1,20,20 UNION SELECT 3,1,40,30 UNION SELECT 4,2,10,10 UNION SELECT 5,2,15,15 UNION SELECT 6,2,20,20 

Then do a self-join to detect missing org2:

 SELECT * FROM #T T1 LEFT JOIN #T T2 ON t1.org1 = t2.org2 AND t1.acc = t2.acc WHERE t2.org1 IS NULL 

Answer 4

Use the left join as Noel Abrahams introduced.

Answer 5

 SELECT * FROM [YorTable] WHERE [Organization1] <> [Organization2] -- The '<>' is read "Does Not Equal".