Author |
Topic |
nevzab
Starting Member
34 Posts |
Posted - 2013-06-18 : 05:12:41
|
Hi,I have been using SQL for a couple of years now, primarily to write views / stored procedures for Crystal Reports. I am looking for some advice regarding the summing of values in a select that references multiple tables. For example... 1. A call is logged (Calls table)2. Labour events are recorded against the call (Call_Events table)3. Parts are issued to the callI need to return...1.The Call_Ref from the Calls table2.The sum of Event_Cost from Call_Events3.The sum of Cost_Price from Call_Parts_UsedI usually use sub-queries as follows:selectCall_Ref,(select sum(call_events.event_cost) from call_events where call_events.link_to_call = calls.call_ref),(select sum(call_parts_used.cost_price) from call_parts_used where call_parts_used.link_to_call = calls.call_ref)from callsOf course, typically, I would be returning a shed load of additional columns that require other tables such as client and equipment related columns.So, my questions are...a. Is this the best approach, given there could be numerous tables involved / columns returned?b. If not then please could you offer advice?Thanks and I look forward to some advice. :-) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:16:51
|
[code]selectCall_Ref,eventcost ,costprice from calls cinner join (select link_to_call,sum(call_events.event_cost) as eventcost from call_events group by link_to_call) ceon ce.link_to_call = c.call_refinner join (select link_to_call ,sum(call_parts_used.cost_price) as costprice from call_parts_used group by link_to_call ) cpon cp.link_to_call = c.call_ref[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-18 : 05:17:31
|
--May be this ?selectCall_Ref,sum(call_events.event_cost),sum(call_parts_used.cost_price) from callsLEFT JOIN call_events ON call_events.link_to_call = calls.call_refJOIN call_parts_used ON call_parts_used.link_to_call = calls.call_refGROUP BY calls.call_ref--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:25:43
|
quote: Originally posted by bandi --May be this ?selectCall_Ref,sum(call_events.event_cost),sum(call_parts_used.cost_price) from callsLEFT JOIN call_events ON call_events.link_to_call = calls.call_refJOIN call_parts_used ON call_parts_used.link_to_call = calls.call_refGROUP BY calls.call_ref--Chandu
why left join for one and inner join for other?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:28:37
|
this is the closest one to main query preserving the correlated subqueries approachselectCall_Ref,eventcost ,costprice from calls couter apply (select sum(event_cost) as eventcost from call_events where link_to_call = c.call_ref)ceouter apply (select sum(cost_price) as costprice from call_parts_used where link_to_call = c.call_ref)cp ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-18 : 05:39:56
|
[code]Hi visakh, --Simple illustrationdeclare @tab1 Table(col1 int, col2 int)insert into @tab1 values(1, 10), (2, 20), (3,30)declare @tab2 Table(col1 int, col2 int)insert into @tab2 values(1, 100), (3,300)declare @tab3 Table(col1 int, col2 int)insert into @tab3 values(1, 110), (2, 210)--My solution with one modificationselectt1.col1,sum(t2.col2) eventcost,sum(t3.col2) costpricefrom @tab1 t1LEFT JOIN @tab2 t2 ON t1.col1 = t2.col1LEFT JOIN @tab3 t3 on t3.col1 = t1.col1GROUP BY t1.col1/*col1 eventcost costprice1 100 1102 NULL 2103 300 NULL*/--Your SolutionSELECT c.col1,eventcost ,costpricefrom @tab1 cinner join (select col1,sum(col2) as eventcost from @tab2 group by col1) ce on ce.col1= c.col1inner join (select col1,sum(col2) as costprice from @tab3 group by col1) cp on cp.col1= c.col1/*col1 eventcost costprice1 100 110*/-- OP's colutionselectc.Col1,(select sum(col2) from @tab2 where c.col1 = col1),(select sum(col2) from @tab3 where c.col1 = col1)from @tab1 c/*col1 eventcost costprice1 100 1102 NULL 2103 300 NULL*/[/code]--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:43:34
|
why not include my last suggestion too? its the closest to OPs original suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-18 : 05:46:07
|
quote: Originally posted by visakh16 why not include my last suggestion too? its the closest to OPs original suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
I haven't seen that before my earlier post--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:58:23
|
quote: Originally posted by bandi
quote: Originally posted by visakh16 why not include my last suggestion too? its the closest to OPs original suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
I haven't seen that before my earlier post--Chandu
Ok..Thats fineI understood reason for the usage of LEFT join, my only query was why you retained second one as inner join itself. Anyways your illustration had it clarified ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-18 : 06:44:05
|
Thanks very much for your alternative examples, however, my SQL returns the same result set as all your examples (where left join is used) so what I would like to know now is which solution is the best and why?Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 06:45:49
|
both left join and outer apply solutions are best ones, dont think there's much to choose between them. You can probably test them for a sample data to compare their performance------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-18 : 06:55:02
|
Okay, thanks you all for your help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 06:57:50
|
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|