Left 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 2005 Forums

Transact-SQL (2005)

Left join

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2008-07-02 : 23:33:32

I know its going to be something I'm doing wrong, but I have the following:

select ri.*,mg.Name,mg.IsrCode,mg.IsrName,mg.IsrPrimary,mg.ticker,mg.name,mg.type,mg.sector,
mg.sectordes,mg.indgroup,mg.indgroupdes,mg.industry,mg.industrydes,mg.subind,mg.subinddes
from RussellIndex_View ri left join MarketQA_EquityGicsData mg
on ri.cusip = mg.cusip left join BloombergEquityPricingData bp on ri.cusip=substring(bp.id_cusip,1,8)]

I dont have any issues with the query until I add the stuff in red, than all the sudden my result set starts showing me several records for the same cusip over and over again. I am always iffy with join, any help would be helpful. I am basically looing to match all the cusips from RussellIndex_View, include some information from MarketQA_EquityGicsData and that works fine, but than I want to also add some info from BloombergEquityPricingData, and match on cusip, there could be cases where the cusip column on the bloomberg side has null values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:26:12

you're getting duplicates because you have one to mant relationship between RussellIndex_View & BloombergEquityPricingData i.e multiple records per cusip. so you need tospecify what value you would be interested from BloombergEquityPricingData so as return that values out of all the values in the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 02:34:13

Why are you LEFT JOINing to table BloombergEquityPricingData without referencing it at all ? Perhaps if you include the columns from BloombergEquityPricingData into your result set you will see why there are duplicates

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources