Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to filter out unwanted data (http://www.go4expert.com/forums/filter-unwanted-data-t756/)

fdtoo 25Apr2006 13:26

How to filter out unwanted data
 
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.

coderzone 25Apr2006 21:09

Re: How to filter out unwanted data
 
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.


All times are GMT +5.5. The time now is 10:23.