|
sqlnovice123
Posting Yak Master
204 Posts |
 Posted - 02/06/2006 : 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 |
|
Another join question
