join excludes things it shouldn't

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

join excludes things it shouldn't

Author

Topic

HockeyFan
Starting Member

26 Posts

Posted - 2008-02-28 : 16:08:35

I've got a problem with this query. I'm not great at joins and so I'm not sure how to fix this.
I have items not in the CMO table (the last join), but I still want them to at least show up. They're being excluded:

SELECT OM.DOCNUM, OM.NAME_LAST, OM.NAME_FIRST, LV.DESCRIPTION AS Sex, ' ' AS Mandatory, CASE WHEN (LSI.Q1 = 1 OR
LSI.Q2 = 1 OR
LSI.Q3 = 1 OR
LSI.Q4 = 1 OR
LSI.Q5 = 1 OR
LSI.Q6 = 1 OR
LSI.Q7 = 1 OR
LSI.Q8 = 1 OR
LSI.Q9 = 1 OR
LSI.Q10 = 1 OR
LSI.Q12 = 1 OR
LSI.Q13 = 1 OR
LSI.Q14 = 1 OR
LSI.Q15 = 1 OR
LSI.Q16 = 1 OR
LSI.Q33 = 1 OR
LSI.Q34 = 1 OR
LSI.Q39 = 1 OR
LSI.Q39 = 0 OR
LSI.Q40 = 1 OR
LSI.Q40 = 0 OR
LSI.Q51 = 1 OR
LSI.Q51 = 0) THEN 'Y' ELSE ' ' END AS Optional, OM.CASE_MANAGER_ID_U AS CASE_MANAGER_ID,
CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1022) THEN 'Y' ELSE ' ' END AS Active, CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1015)
THEN 'Y' ELSE ' ' END AS Complete
FROM dbo.OFFENDER_MAIN AS OM LEFT OUTER JOIN
dbo.LOOKUP_VALUES AS LV ON LV.ID = OM.GENDER_ID_LV LEFT OUTER JOIN
dbo.LSI_ANSWERS AS LSI ON LSI.DOCNUM = OM.DOCNUM LEFT JOIN
dbo.COMMUNITY_MOD_OBJ AS CMO ON CMO.DOCNUM = OM.DOCNUM
WHERE (LSI.COMIT_ACTIVE = 1) AND (CMO.OBJECTIVE_CODE_ID_LV = 1011) AND (CMO.REMOVAL_RSN_ID_LV = 1022 OR
CMO.REMOVAL_RSN_ID_LV = 1015)
AND (OM.FACILITY_ID_F <> 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:20:13

[code]SELECT OM.DOCNUM,
OM.NAME_LAST,
OM.NAME_FIRST,
LV.DESCRIPTION AS Sex,
' ' AS Mandatory,
CASE
WHEN 1 IN (LSI.Q1, LSI.Q2, LSI.Q3, LSI.Q4, LSI.Q5, LSI.Q6, LSI.Q7, LSI.Q8) THEN 'Y'
WHEN 1 IN (LSI.Q9, LSI.Q10, LSI.Q12, LSI.Q13, LSI.Q14, LSI.Q15, LSI.Q16) THEN 'Y'
WHEN 1 IN (LSI.Q33, LSI.Q34, LSI.Q39, LSI.Q40, LSI.Q51) THEN 'Y'
WHEN 0 IN (LSI.Q39, LSI.Q40, LSI.Q51) THEN 'Y'
ELSE ' '
END AS Optional,
OM.CASE_MANAGER_ID_U AS CASE_MANAGER_ID,
CASE CMO.REMOVAL_RSN_ID_LV
WHEN 1022 THEN 'Y'
ELSE ' '
END AS Active,
CASE CMO.REMOVAL_RSN_ID_LV
WHEN 1015 THEN 'Y'
ELSE ' '
END AS Complete
FROM dbo.OFFENDER_MAIN AS OM
LEFT JOIN dbo.LOOKUP_VALUES AS LV ON LV.ID = OM.GENDER_ID_LV
LEFT JOIN dbo.LSI_ANSWERS AS LSI ON LSI.DOCNUM = OM.DOCNUM
AND LSI.COMIT_ACTIVE = 1
LEFT JOIN dbo.COMMUNITY_MOD_OBJ AS CMO ON CMO.DOCNUM = OM.DOCNUM
WHERE CMO.OBJECTIVE_CODE_ID_LV = 1011
AND CMO.REMOVAL_RSN_ID_LV IN (1022, 1015)
AND OM.FACILITY_ID_F <> 1[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources