Join error, extra rows ? - 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

General SQL Server Forums

New to SQL Server Programming

Join error, extra rows ?

Author

Topic

trento
Starting Member

13 Posts

Posted - 2010-02-19 : 16:12:05

Hi, all
I have some strange issue after modifying my old simple select trying to replace result column based on corresponding flag value in another table, new Select produces more !!!! rows then in base table, so I’m totally confused. Can anybody give a hint what is wrong here,

Old select row count=232072 (all rows in table returned)
SELECT
RIGHT(ot.OldCustom, 6) +
cast(YEAR(ot.OpenDate) as char(4))+
FROM customfile ot

New select row count= 232272 (200 extra rows !!!):
SELECT
case when ob.NewCustFlag= 1 then RIGHT(ot.NewCustom, 6)
else RIGHT(ot.OldCustom, 6) end +
cast(YEAR(ot.OpenDate) as char(4))
FROM customfile ot
inner join CustomStat ob on ot.OldCustom = ob.Custom

Additional info:
Select count(*) from customfile = 232072 (match Old Select result)
Select count (*) from customfile ot where not exist
(select * from CustomStat where ot .OldCustom = ob.Custom -- = 0 (all exists for inner join)

Thanks
Trent

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-19 : 16:22:08

sure youre probably not joining on the full key

Post the ddl f the tables, including keys

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 00:00:11

seems like you've 1 to many relationship existing b/w customfile and CustomStat tables. so if you want single result per customfile you need to apply grouping over customfile fields and apply some kind of aggregation (sum,count,min,max,..) over other tables columns.

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

subhash chandra
Starting Member

40 Posts

Posted - 2010-02-21 : 06:36:14

Due to the one to many relationship there are duplicate records in output. Its sure that out of 232272 records distinct values are not more than 232072. So you can remove the duplicate values using DISTINCT clause and test if that result works.

Regards,
Subhash Chandra
Founder: http://SQLReality.com/blog/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 08:47:54

quote:
Originally posted by subhash chandra

Due to the one to many relationship there are duplicate records in output. Its sure that out of 232272 records distinct values are not more than 232072. So you can remove the duplicate values using DISTINCT clause and test if that result works.

Regards,
Subhash Chandra
Founder: http://SQLReality.com/blog/

DISTINCT wont work as long as you're returning CustomStat fields also in resultset. for that you need to apply group by over customfile fields

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources