Programming Forums > Script Programming > Other Scripting Languages

jonyzz · Sep 5th, 2007, 10:44 AM

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:

(Toggle Plain Text)

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

jonyzz · Sep 5th, 2007, 10:51 AM

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:

(Toggle Plain Text)

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

Sep 5th, 2007, 10:44 AM	#1
jonyzz Programmer Join Date: Aug 2005 Location: null Posts: 40 Rep Power: 0	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: (Toggle Plain Text) SELECT [Order].ClientId, SUM(OrderItem.QuantityOrderItem.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.QuantityOrderItem.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 SELECT [Order].ClientId, SUM(OrderItem.QuantityOrderItem.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.QuantityOrderItem.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

Sep 5th, 2007, 10:51 AM	#2
jonyzz Programmer Join Date: Aug 2005 Location: null Posts: 40 Rep Power: 0	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: (Toggle Plain Text) SELECT Client.[Name], b.Buy - s.Sell AS 'Turnover' FROM Client INNER JOIN ((SELECT [Order].ClientId, SUM(OrderItem.QuantityOrderItem.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.QuantityOrderItem.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 SELECT Client.[Name], b.Buy - s.Sell AS 'Turnover' FROM Client INNER JOIN ((SELECT [Order].ClientId, SUM(OrderItem.QuantityOrderItem.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.QuantityOrderItem.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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
complete newbie question	melee28	C	36	Sep 11th, 2005 6:48 AM
How Do I: Rewrite a Delphi 6 console app to run as a DLL for IIS5	daemonreaver	Delphi	0	Mar 9th, 2005 2:05 PM
airport Log program using 3D linked List : problem reading from file	gemini_shooter	C++	0	Mar 2nd, 2005 4:12 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Script Programming > Other Scripting Languages

SQL: Rewrite a complex join without #temp tables?