Query results

[BFarley]

I have two queries that run fairly quickly, and would like the results of each to appear in a single grid, without using a temp table.

Here's how I'm doing the selection ...

SELECT AcctNo, AcctName, SUM(TransAmt) AS Table1Total
FROM Table1
WHERE TransDate BETWEEN '4/1/08' AND '5/1/08'
GROUP BY AcctNo

SELECT AcctNo2, AcctName2, SUM(TransAmt2) AS Table2Total
FROM Table2
WHERE TransDate2 BETWEEN '4/1/08' AND '5/1/08'
GROUP BY AcctNo2

... and here's how I'd like the output to look:

123456	Fudd     224,117.69	224,117.50
234567	Duff     339,775.91	339,577.19
345678	Fbar     982,833.75	833,982.57

UNION and UNION ALL don't seem to produce the desired results.

My brain is stuck on how to do it using a temp table or table variable, and I'm pretty sure I'm overlooking something VERY obvious.

Thanks in advance!


Bryant Farley
EG&G Technical Services

 

[genomon]

Without knowing your table structures and data, and assuming there is a foreign key relationship between them (TransAmt/TransAmt2 ?), wouldn't a simple INNERR JOIN work?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[BFarley]

Actually I've tried that ... and 3 min later, still running, even though each individual query returns data in < 2 sec.

That being the case ... thinking maybe there's something in the data structure and I need to understand it better.

And sorry to all about the very vague subject heading -- I didn't edit it before pressing send


Bryant Farley
EG&G Technical Services

 

[genomon]

Take a look at the query execution plan with the INNER JOIN.
It could be you need to add an index or indices to your tables (otherwise you get a full table scan for each datum returned).

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."