little help with some tsql
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.
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)
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)
Here is a 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 perform a self-join to discover 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
SELECT
*
FROM
[YorTable]
WHERE
[Organization1] <> [Organization2] -- The '<>' is read "Does Not Equal".
Use left join as Noel Abrahams presented.
精彩评论