Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
romeck
Starting Member
16 Posts |
Posted - 2012-11-12 : 17:15:13
|
i have 2 tables like thisX {id, value1}Y{id,idx,value2}X data1 15002 4003 1004 10005 400Y data1 1 2002 1 4003 3 1004 2 100How should i make join query that return 1 1500 600 9002 400 100 3003 100 100 04 1000 0 10005 400 0 400Which basicaly is 1) x.idx, 2)x.value, 3)sum(value2 from y where y.idx=x.id), 4) 2)-3)which query is faster ?select id ,value , select(sum(value2) from y where y.idx=x.id) as sums, value - sums from x or the join query i ask you to create. THQ |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-12 : 19:55:13
|
[CODE]select x.id, x.value1, sum(y.value2), x.value1 - sum(y.value2)from xinner join y on x.id = y.idxgroup by x.id, x.value1[/CODE]Having the subquery in the SELECT caluse, as you have done it, would probably have the subquery execute for every record returned and therefore would be slower. The SQL Engine might be smart enough to figure out what you have done and fix things under the covers but it might not.=================================================We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
|
|
romeck
Starting Member
16 Posts |
Posted - 2012-11-19 : 15:42:04
|
but as i use inner join then in output i do not have line 4 coouse it do not have any records in Y table. and i neeed to have all records from table X despite of any records for it in Y. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-19 : 15:54:58
|
You mean this one:Select x.id,x.Value1,YSUM,ISNULL(Value1,0) - YSUMfrom xinner join(Select x.id,SUM(isnull(y.value2,0))YSUMfrom xleft join y on x.id = y.idxgroup by x.id)y on x.id = y.id |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-20 : 01:50:26
|
[code]SELECT m.id,m.value1,COALESCE(n.Tot,0) AS Tot,m.value1-COALESCE(n.Tot,0) AS ResFROM x mLEFT JOIN (SELECT idx,SUM(value2) AS Tot FROM y GROUP BY idx )nON n.idx = m.id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|