SQL Server Performance Forum – Threads Archive

How to filter out unwanted data

I have the following fields in table A: Date | Descrip | Amt Dr
———————————-
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 41142 | 1,800.00
20/02/2006 | 41142 | 2,700.00
25/02/2006 | 740245 | 5,200.00 I have the following fields in table B: Date | Descrip | Amt Dr
———————————-
02/02/2006 |88258 | 1,400.00
17/02/2006 |740244 | (1,500.00)
25/02/2006 |740245 | 5,200.00 There are no referencial key between TableA & TableB,
What i want is to extract the date,descrip & Amt data from
Table A where it’s Descrip data is not the same as the data
in Table B’s Descrip column. My sql syntax is as follows:
SELECT
CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Amt – (TableA.Amt * 2)ELSE 0 END AS [Add Back]
FROM TableA,TableB
WHERE TableA.Descrip <> TableB.Descrip
GROUP BY TableA.Date,TableA.Amt,TableA.Descrip The output of the above is as follows: Date | Descrip | Amt Dr
———————————-
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 0 | 0
20/02/2006 | 0 | 0
25/02/2006 | 740245 | 5,200.00 Notice that, descrip with 740245 appearing in both tables
is what the sql should filter out, but failed to do so,
what i want is as below:
Date | Descrip | Amt Dr
———————————-
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
Can i achieve this? Please help.

Add this to your WHERE statement AND NOT EXISTS (SELECT * FROM TableB WHERE TableB.Descrip = TableA.Descrip)

Select * from TableA where not exists(
select description from TableB) Madhivanan Failing to plan is Planning to fail

Madhivanan, the subquery has to be correlated – you have to add a WHERE statement, like I did. Your query will only return rows if TableB is empty.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Madhivanan, the subquery has to be correlated – you have to add a WHERE statement, like I did. Your query will only return rows if TableB is empty. <hr height="1" noshade id="quote"></blockquote id="quote"> Yes it is. Thanks Adriaan [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Your requirements are still not clear. You said you wanted to exclude just one row from table A existing in the table B but your result set doesn’t reflect that.

]]>