I have the following fields in table A:
Code:
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:
Code:
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:
Code: SQL
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:
Code:
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:
Code:
Date	    | Descrip |  Amt Dr  
---------------------------------- 
01/02/2006  |  740240  |(2,400.00)
14/02/2006  |  740241  |(3,000.00)
Can i achieve this? Please help.

Last edited by shabbir; 25Apr2006 at 20:50.. Reason: Proper formating