Programming Forums - SQL: Rewrite a complex join without #temp tables?

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Other Scripting Languages (http://www.programmingforums.org/forum39.html)

- - SQL: Rewrite a complex join without #temp tables? (http://www.programmingforums.org/showthread.php?t=13919)

SQL: Rewrite a complex join without #temp tables?

Hello, guys
I have an example query which includes a complex join with 4 tables (actually 5, because one of them must be aliased twice). I could not find another option but to use #temporary tables to store some of the inner joins. Do any of you have any idea how could this query be rewritten without using the temporary tables?

There are the following tables: Client (Id, Name), OrderType (Id, Name = {'sell', 'buy'}), Order(Id, ClientId, OrderTypeId), OrderItem (OrderId, Quantity, Price)

I need to get the following result: A table with the names of the clients that made a positive turnover:

Client name | Turnover

Where each client's Turnover is calculated as the difference between the sum of all Orders of type 'buy' and the sum of all Orders of type 'sell'

or turnover = (SUM(buy) - SUM(sell))

I am quite puzzled, need to confess I haven't written many complex queries by now. Here is what I came to:



SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Sell'

INTO #Sell

FROM OrderItem 

        INNER JOIN [Order] 

        ON [Order].Id = OrderItem.OrderId

WHERE [Order].OrderTypeId=

        (SELECT Id FROM OrderType WHERE [Name] = 'sell')

GROUP BY([Order].ClientId)



SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Buy'

INTO #Buy

FROM OrderItem 

        INNER JOIN [Order] 

        ON [Order].Id = OrderItem.OrderId

WHERE [Order].OrderTypeId=

        (SELECT Id FROM OrderType WHERE [Name] = 'buy')

GROUP BY([Order].ClientId)



SELECT Client.[Name], b.Buy - s.Sell AS 'Turnover'

FROM Client 

        INNER JOIN

                (#Buy AS b

                INNER JOIN #Sell AS s

                        ON b.ClientId = s.ClientId)

        ON Client.Id = s.ClientId

WHERE (b.Buy - s.Sell) > 0

It works fine but I was wondering if I could skip the #temp tables.
If you have any ideas I will appreciate.
Thanks :)

Yep I just made a nice experiment and it turned out it works !!! :) Maybe I was too fast in posting the question when I was just on the edge of the answer :) but I have been scratching my head upon this query for two days :) Well it might be useful for someone anyway, so I will post my creation :):) All I did was to paste the code that creates the #temp tables in the places of their names in the last query. And put brackets around it as well. Here it is:



SELECT Client.[Name], b.Buy - s.Sell AS 'Turnover'

FROM Client 

        INNER JOIN

                ((SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Buy'

                        FROM OrderItem 

                                INNER JOIN [Order] 

                                ON [Order].Id = OrderItem.OrderId

                        WHERE [Order].OrderTypeId=

                                (SELECT Id FROM OrderType WHERE [Name] = 'buy')

                        GROUP BY([Order].ClientId)) AS b

                INNER JOIN (SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Sell'

                                        FROM OrderItem 

                                                INNER JOIN [Order] 

                                                ON [Order].Id = OrderItem.OrderId

                                        WHERE [Order].OrderTypeId=

                                                (SELECT Id FROM OrderType WHERE [Name] = 'sell')

                                        GROUP BY([Order].ClientId)) AS s

                        ON b.ClientId = s.ClientId)

        ON Client.Id = s.ClientId

WHERE b.Buy - s.Sell > 0