Why would a SQL query JOIN on the same table twice with the same condition?
- by Scott Leis
I'm working on changes to a SQL Server v8 database developed by someone else, and have found something that seems (based on my limited SQL knowledge) strange and pointless.
One of the views has a query that does a LEFT OUTER JOIN twice on the same table with the same condition. Is there any reason for doing this?
The query is below. See the second- and third-last lines that both join the "te_SDE_Survey" table on the "SDE_ID" field. Also note these lines set two different aliases for the table, and both aliases are used in the SELECT part of the query.
SELECT vs.SLMS_Code, vs.Retail_Date, vs.TagNo, vs.Rego, vs.Model, vs.Company,
vs.AccountType, viqdp.SDE_ID, bd.Debit_Date, isu.Survey_Date,
CASE
WHEN isu.Q6 IS NOT NULL THEN isu.Q6
ELSE CASE WHEN returned_surveys.survey_date IS NULL THEN
CASE WHEN (viqdp.expiryDate < getdate() AND cs.sup1 IS NULL AND cs.sup2 IS NULL
AND cs.sup3 IS NULL AND cs.sup5 IS NULL AND cs.sup8 IS NULL AND cs.sup9 IS NULL) THEN 'E'
WHEN (viqdp.expiryDate < getdate() AND cs.sup1 = 'F' AND cs.sup2 = 'F' AND cs.sup3 = 'F'
AND cs.sup5 = 'F' AND cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'E'
WHEN cs.sup1 = 'T' THEN 'S'
WHEN cs.sup2 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
WHEN cs.sup3 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
WHEN cs.sup5 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
WHEN cs.sup8 = 'T' AND (cs.sup2 = 'F' AND cs.sup3 = 'F' AND cs.sup5 = 'F') THEN 'E'
WHEN cs.sup9 = 'T' AND (cs.sup2 = 'F' AND cs.sup3 = 'F' AND cs.sup5 = 'F') THEN 'E'
WHEN (cs.sup8 = 'T' OR cs.sup9 = 'T') AND (cs.sup2 = 'T' OR cs.sup3 = 'T' OR cs.sup5 = 'T') THEN 'S'
END
WHEN (tey.survey_expire_method = 'pre2008') THEN
CASE WHEN (datediff(month, viqdp.generate_date, returned_surveys.survey_date) 1) THEN 'E' END
WHEN (tey.survey_expire_method = 'expiryDateColumn') THEN
CASE WHEN (returned_surveys.survey_date viqdp.expiryDate) THEN 'E' END
END
END AS score_or_exclusion_status,
CASE WHEN (bd.explanation IS NULL) THEN '' ELSE bd.explanation END AS explanation,
tey.te_Year
FROM dbo.te_Vehicle_Sale vs INNER JOIN
dbo.te_Year tey ON vs.Retail_Date = tey.Start_Date AND vs.Retail_Date <= tey.End_Date LEFT OUTER JOIN
dbo.Bad_Data bd ON vs.TagNo = bd.TagNo LEFT OUTER JOIN
dbo.te_Vehicle_SDESurvey viqdp ON vs.TagNo = viqdp.TagNo LEFT OUTER JOIN
dbo.te_SDE_Survey isu ON viqdp.SDE_ID = isu.SDE_ID LEFT OUTER JOIN
dbo.te_SDE_Survey returned_surveys ON viqdp.SDE_ID = returned_surveys.SDE_ID LEFT OUTER JOIN
dbo.te_SDE_Contact_Suppression cs ON viqdp.SDE_ID = cs.SDE_ID
