Problem with Join Statement - 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 2005 Forums

Transact-SQL (2005)

Problem with Join Statement

Author

Topic

mee12
Starting Member

1 Post

Posted - 2008-08-06 : 06:30:26

Hi

I am having a problem with a Left Join statment. The problem is when I join two tables which uses a unique code (ISIN). The problem is when I run the query it shows all the records but duplicates the records that have matching ISIN.

Any help would be very much appreciated.

Thanks in advance

select distinct syn.isin, tblTERsynExternal.ClassName,
tblTERsynExternal.TotalTER, tblTERsynExternal.CombinedMgtPerfTer, tblTERsynExternal.OtherTER,
tblTERsynExternal.date,tblTERsynExternal.SourceFile
from tblTERsyn Syn
left join tblTERsynExternal on Syn.Isin = tblTERsynExternal.isin and syn.navdate = (select max(tblTERsyn.navdate) from tblTERsyn where tblTERsyn.isin = tblTERsynExternal.isin and tblTERsyn.navdate between '20 Mar 2008' and '20 Jun 2008')
where syn.fundcode = 'DB5032'
order by syn.isin

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-06 : 10:35:15

a) You have not described why this is wrong, only that it is not what you want
b) What do you want?
c) Even without understanding what you want, DISTINCT is mostly going to be wrong.
Maybe post some data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:07:34

quote:
Originally posted by mee12

Hi

I am having a problem with a Left Join statment. The problem is when I join two tables which uses a unique code (ISIN). The problem is when I run the query it shows all the records but duplicates the records that have matching ISIN.

Any help would be very much appreciated.

Thanks in advance

select distinct syn.isin, tblTERsynExternal.ClassName,
tblTERsynExternal.TotalTER, tblTERsynExternal.CombinedMgtPerfTer, tblTERsynExternal.OtherTER,
tblTERsynExternal.date,tblTERsynExternal.SourceFile
from tblTERsyn Syn
left join tblTERsynExternal on Syn.Isin = tblTERsynExternal.isin and syn.navdate = (select max(tblTERsyn.navdate) from tblTERsyn where tblTERsyn.isin = tblTERsynExternal.isin and tblTERsyn.navdate between '20 Mar 2008' and '20 Jun 2008')
where syn.fundcode = 'DB5032'
order by syn.isin

i think what you're thinking as duplicates are not real duplicates. The reason you think they are duplicates may be because there is one to many relationship between two tables. so you will get fields of first table duplicated for each values of second. You could probably provide some data sample to explain what you mean by duplicates and also sample output which you're looking at.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources