April 25, 2006 at 2:11 am
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.
April 25, 2006 at 2:49 am
Hello,
did you post all relevant data in both tables? If yes, then I don't understand why do you want to display the two rows in the result, but not
15/02/2006 | 41142 | 1,800.00
20/02/2006 | 41142 | 2,700.00
In all these rows, I couldn't find anything matching in table B. In fact, the only row (and also the only description, and the only date) that occurs in both tables, is
25/02/2006 | 740245 | 5,200.00
Also, I am not sure whether "The output of the above is as follows:" is correct, because then the result would be different... e.g., there is no [Add Back] column in the result. But that's maybe only typo when describing the output.
BTW, should I understand that (1900) means -1900? You have some CASE statements there, and the result clearly shows that SQL Server does not consider the numbers in parentheses being in minus. I think I've seen someone using parentheses for negative numbers, but the correct way is to use -1900.
In any case, please try to describe your needs in more detail - as it is, I couldn't make out what so you need to do - and therefore I can't tell why your query does not work.
Just as a test, from "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" I would make such query:
SELECT <column_list>
FROM TableA a
JOIN TableB b ON a.date = b.date AND a.amt=b.amt AND a.descrip <> b.descrip
It can be a complete nonsense though, because as I said I'm not sure what you need.
HTH, Vladan
April 25, 2006 at 6:24 am
There are a couple ways to go about this, depending on some more details about how you want to go about joining/filtering the tables.
SELECT
FROM TableA
WHERE TableA.Descrip NOT IN (SELECT TableB.Descrip FROM TableB)
That's the simplest solution, but not the most efficient.
SELECT
FROM TableA
LEFT JOIN TableB
ON TableA.Descrip = TableB.Descrip
WHERE TableB.Descrip IS NULL
This is a better solution. It joins the tables together, then filters out all the records where the two actually are equal.
Your solution (using the join) actually is creating a giant cross-product of the two tables, filtering out any records where the two Descrip fields are equal, and then rolling the results back up with the GROUP BY. You're getting the 740245 result because you have the results
25/02/2006 740245 5,200.00 02/02/2006 88258 1,400.00
25/02/2006 740245 5,200.00 17/02/2006 740244 (1,500.00)
included in your cross-product. Thus, when you perform the GROUP BY, 740245 still shows up.
Hope that helps.
April 25, 2006 at 8:14 am
Same question here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65167
...and here...
No clarification of the problem on any of them yet...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply