How to filter out unwanted data

[fdtoo]

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.

[chazzy]

you should only need to select from tableA


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
WHERE TableA.Descrip not in (SELECT Descrip FROM tableB)