Another join question - 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

Another join question

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-06 : 13:51:03

Hello,

Based on the code below, I notice that the Left Join tries to match any row in the same table(db..external_id_snapshot) twice based on the values 1 and 2 for the column external_id_type. Is this the simplest, best and the only way of implementing the Left Join or are there other ways?

LEFT JOIN db..external_id_snapshot_table sedol ON sec.security_id = sedol.security_id AND sedol.external_id_type = 2
LEFT JOIN db..external_id_snapshot_table cusip ON sec.security_id = cusip.security_id AND cusip.external_id_type = 1

SELECT ModelID = today.ModelID
, RelativeUniverseID = today.RelativeUniverseID
, SecurityGroupID = today.SecurityGroupID
, SecurityID = today.SecurityID
, ModelValueT = CONVERT(decimal (8, 4), today.ModelValue)
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), today.ModelValue-yest.ModelValue)
, tCapUSD = CONVERT(varchar(17), tSGF.cap_usd, 1)
, tBPR = tSGF.BPR
, tEPR = tSGF.EPR
, tCEPR = tSGF.CEPR
, tFCEPR = tSGF.FCEPR
, tFEPR = tSGF.FEPR
, tMOMR = tSGF.MOMR
, yCapUSD = CONVERT(varchar(17), ySGF.cap_usd, 1)
, yBPR = ySGF.BPR
, yEPR = ySGF.EPR
, yCEPR = ySGF.CEPR
, yFCEPR = ySGF.FCEPR
, yFEPR = ySGF.FEPR
, yMOMR = ySGF.MOMR
, Name = sec.name
, EXT_ID = ISNULL(cusip.external_id, sedol.external_id)
, CountryName = cty.name
, IndustryName = ind.name
FROM OMdb.dbo.AP_SecurityGroupModelValues today
JOIN dbArchive.dbo.APSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = 20060204 /*@DataDate*/
JOIN OMdb.dbo.AP_SecurityGroupMembers mem
ON mem.SecurityID = today.SecurityID
AND mem.UniverseID = 4 /*@UniverseID*/
AND mem.GroupClassID = 1
JOIN #today tSGF
ON today.SecurityGroupID = tSGF.SecurityGroupID
JOIN #yest ySGF
ON yest.SecurityGroupID = ySGF.SecurityGroupID
JOIN OMdb..APSecurities gsec ON today.SecurityID = gsec.SecurityID
JOIN db..security_table sec ON today.SecurityID = sec.security_id
LEFT JOIN db..external_id_snapshot_table sedol ON sec.security_id = sedol.security_id AND sedol.external_id_type = 2
LEFT JOIN db..external_id_snapshot_table cusip ON sec.security_id = cusip.security_id AND cusip.external_id_type = 1
JOIN db..iv_eq_security_table esec ON today.SecurityID = esec.security_id
JOIN db..country_table cty ON esec.primary_country = cty.country
JOIN db..industry_table ind ON esec.primary_industry = ind.industry AND esec.industry_type = ind.industry_type
WHERE today.ModelID = 4/*@ModelID*/
AND ABS(today.ModelValue-yest.ModelValue) >= 0 /*@PerentChange*/
AND gsec.CapUSDMM >= ISNULL(gsec.CapUSDMM, 0.3)/*@CapThreshold)*/
AND /*(@FilterSecurityGroupID*/ 39123 IS NULL
OR /*(@FilterSecurityGroupID*/(39123 IS NOT NULL AND today.SecurityID IN (SELECT SecurityID FROM OMdb..AP_SecurityGroupSecurities WHERE SecurityGroupID = 39123/*@FilterSecurityGroupID*/ ) )
--)

Thanks in advance!
sqlnovice123

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 14:07:30

What this code is doing is joining to 2 instances of the same table (external_id_snapshot_table). Once for sedol rows and once for cusip rows. The fact that they are left outer joins is just so that security_table rows won't be omitted if there are now matching rows in external_id_snapshot_table.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources