How to filter out unwanted data

fdtoo's Avatar, Join Date: Apr 2006
Go4Expert Member
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
0
coderzone's Avatar, Join Date: Jul 2004
Team Leader
Why do you need the group by clause the row that is coming unwanted check if there is any traling spaces in any of the tables because those record that are equal in both table should not be coming according to the Where clause.