self inner join - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

self inner join

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-15 : 07:03:20

I am unable to explain why these two queries give two different sum value.
Do you see why please?
Thanks

SELECT SUM(tbl1.BV), BCcy FROM tbl1 INNER JOIN tblMain
ON tbl1.PCode = tblMain.BCode
WHERE tbl1.PCode='xx' and
tbl1.AsOfDate = '23 jan 2005'
GROUP BY BCcy

------------------------
SELECT SUM(BE1.BV), BCcy FROM tbl1 as BE1 INNER JOIN tblMain
ON BE1.PCode = tblMain.BCode INNER JOIN tbl1 BE2 ON
BE1.PCode = BE2.PCode AND BE1.AsOfDate = BE2.AsOfDate
WHERE BE1.PCode='xx' and
BE1.AsOfDate = '23 jan 2005'
GROUP BY BCcy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:13:07

that because of addition join

INNER JOIN tbl1 BE2 ON
BE1.PCode = BE2.PCode AND BE1.AsOfDate = BE2.AsOfDate

in first case it will return count irrespective of above relationship whereas in second case it will put further check in place to make sure that above relationship holds good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-15 : 07:17:53

Do not understand. Can you please elaborate further?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:21:00

are there multiple records in tbl1 with same values for PCode and AsOfDate ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-15 : 09:06:25

quote:
Originally posted by visakh16

are there multiple records in tbl1 with same values for PCode and AsOfDate ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:18:46

then it will cause it to return more rows as duplicates on joining yield more rows (ie 2 rows with same value for columns on joining returns 2 * 2 =4 rows)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:23:20

see this example


declare @tbl table
(
id int
)

insert @tbl
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 

select *
from @tbl t1
join @tbl t2
on t2.id = t1.id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-15 : 09:24:16

HOw can I make the second query to return the same sum as the first query?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:29:23

depending on your requirement take distinct based on column with duplicate values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-15 : 09:30:54

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:06:46

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources