how to select the odd records

Question

I have this tableA

PK, debit, Credit, account

1, 1, null, Account1
2, null, 1, Account1
3, 1, null, Account1
4, 1, null, Account2

I want you to help me a sql command to select the 3rd and 4th records. Means that I want all records except the records with debit=credit & Account=Account

My sql command :

SELECT [TableA].*
   FROM [TableA] LEFT JOIN
      (SELECT [TableA_3].*
         FROM [TableA] AS [TableA_3] INNER JOIN
            [TableA] AS [TableA_1] 
            ON [TableA_3].debit = [TableA_1].credit 
            AND [TableA_3].account = [TableA_1].account)
         AS [TableA_2] 
      ON [TableA].PK = [TableA_2].PK
WHERE [TableA_2].PK IS NULL

only select the 4th record because the TableA_2 is contained first 3 records.

Thanks for your help.

Answer 1

SELECT [TableA].*
   FROM [TableA] LEFT JOIN
      ((SELECT [TableA_1].*
         FROM [TableA] AS [TableA_3] INNER JOIN
            [TableA] AS [TableA_1] 
            ON [TableA_3].debit = [TableA_1].credit 
            AND [TableA_3].account = [TableA_1].account)
            Union
            (SELECT [TableA_3].*
         FROM [TableA] AS [TableA_3] INNER JOIN
            [TableA] AS [TableA_1] 
            ON [TableA_3].debit = [TableA_1].credit 
            AND [TableA_3].account = [TableA_1].account))
         AS [TableA_2] 
      ON [TableA].PK = [TableA_2].PK
WHERE [TableA_2].PK IS NULL

based your desired output, may be this what you need...

---

Hope it Helps!!

Answer 2

;With cteDebit As
(Select PK, debit, Credit, account, Row_Number() Over(Partition By account, debit Order By PK) As rn 
From TableA
Where debit Is Not Null),
cteCredit As
(Select PK, debit, Credit, account, Row_Number() Over(Partition By account, Credit Order By PK) As rn 
From TableA
Where Credit Is Not Null)
Select Coalesce(c.PK, d.PK) As PK,
  Coalesce(c.debit, d.debit) As debit,
  Coalesce(c.Credit, d.Credit) As Credit,
  Coalesce(c.account, d.account) As account
From cteDebit d
Full Outer Join cteCredit c On c.account = d.account And c.Credit = d.debit And c.rn = d.rn
Where c.PK Is Null Or d.PK Is Null
Order By Coalesce(c.PK, d.PK);

Tom

Answer 3

Tks Stan, but it still return only the 4th record.

Answer 4

is that not your desired output?? what is your desired output?? from your question, my impression was you only need to return 4th row....

---

Hope it Helps!!

Answer 5

pls reread it, I wanna select the 3,4 records.

the output i need is :

PK, debit, Credit, account
3, 1, null, Account1
4, 1, null, Account2

Answer 6

the query running exactly as what I want and extremely fast.

Tks so much Tom.

Answer 7

Hi Tom, could you pls drop me some lines on this thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/956ff71b-d711-4cb0-b0d9-c360c777d61c. I'd like this syntax because it's faster.