Derived tables - is there a more elegant way.
-
Hi - I'm trying to join 3 tables (left outers on two) and sum the output. Can my select be re-writen to avoid the derived tables?
CREATE TABLE #tblAccount (ID INT)
INSERT INTO #tblAccount VALUES(1)
INSERT INTO #tblAccount VALUES(2)
INSERT INTO #tblAccount VALUES(3)
INSERT INTO #tblAccount VALUES(4)
CREATE TABLE #tblA (ID INT, Quantity INT)
INSERT INTO #tblA VALUES (1,10)
INSERT INTO #tblA VALUES (2,20)
INSERT INTO #tblA VALUES (2,40)
INSERT INTO #tblA VALUES (3,80)
CREATE TABLE #tblB (ID INT, Quantity INT)
INSERT INTO #tblB VALUES (2,160)
INSERT INTO #tblB VALUES (3,320)
INSERT INTO #tblB VALUES (3,640)
INSERT INTO #tblB VALUES (3,1)
SELECT
ac.ID,
COALESCE(a.AQuantity,0) AS AQuantity,
COALESCE(b.BQuantity,0) AS BQuantity
FROM
#tblAccount ac LEFT OUTER JOIN
(SELECT
ID,
SUM(Quantity) AS AQuantity
FROM
#tblA
GROUP BY
ID) a
ON ac.ID = a.ID
LEFT OUTER JOIN
(SELECT
ID ,
SUM(Quantity) AS BQuantity
FROM
#tblB
GROUP BY
ID) b
ON ac.ID = b.ID
DROP TABLE #tblAccount
DROP TABLE #tblA
DROP TABLE #tblB
-
Yes, not only can you do this, but If you create the same statements using the left outer join syntax, run both statements in the same query window and turn on Execution plan results, you will see that the one you show above, takes 1/3rd of the total time, where the alternative takes 2/3rds.
In other words, it's also more efficient, from the testing I just did......but doesn't guarantee it will always be so, just that it is in your example.....
I was bored, and curious.....
-
Scorpion - Have you been drinking?
Allen - Nicely written question
You've done this exactly how I'd have done it, and I don't see a more elegant (or efficient) way...Scorpion - what was your alternative (if indeed you had one)?
Anyone else?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
You can save on one derived table by doing this...you cannot avoid both.
SELECT
ac.ID,
COALESCE(sum(a.Quantity),0) AS AQuantity,
COALESCE(max(b.BQuantity),0) AS BQuantity
FROM
#tblAccount ac
LEFT OUTER JOIN #tblA a
ON ac.ID = a.ID
LEFT OUTER JOIN (SELECT ID, SUM(Quantity) AS BQuantity
FROM #tblB
GROUP BY ID) b
ON ac.ID = b.ID
group by ac.id
This will work since the derived table will always get one record per ID so the MAX will ensure the right value...and for the first one, a derived table won't be needed because of the 1:N (one to many) relationship between #tblAccount and #tblA and the grouping done on AC.ID...the second one cannot be avoided because of the another 1:N relationship between #tblAccount and #tblB.
The performance characteristics will depend upon the data volume and the filter criteria on indexed columns that gets provided to the query.
-
> the second one cannot be avoided because of the another 1:N relationship between #tblAccount and #tblB.
?????
In the query we have:
ac.ID = a.ID = b.ID
"group by ac.id" actuall means group by a.id and group by b.id as well.
SELECT
ac.ID,
COALESCE(sum(a.Quantity),0) AS AQuantity,
COALESCE(sum(b.BQuantity),0) AS BQuantity
FROM
#tblAccount ac
LEFT OUTER JOIN #tblA a ON ac.ID = a.ID
LEFT OUTER JOIN #tblB b ON ac.ID = b.ID
group by ac.id
should work fine.
_____________
Code for TallyGenerator -
This is one of the options I tried, and can tell you that the results are incorrect like this. Run the two statements together, and you'll see what I am talking about.
-
Not only you've been drinking today.
_____________
Code for TallyGenerator -
The one from Sergiy does not work (sorry) but I understand why. I'ts actually where I started. The one from rsharma does work - now I just need to understand it - I have the notes to help me!
Thanks for all the replies.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply