Thread: JOIN condition confusion
Hi, I am wondering if there is a way to set conditions on a left joined table without hosing the join altogether: query = qq |SELECT p.*, a.user_id FROM patient_dosing p LEFT JOIN patient_assignment a ONp.patient_id = a.patient_id WHERE p.dose_type = 'Missed (AWOL)' AND (p.dose_date >= $start_date ANDp.dose_date <= $end_date) -- the next two conditions hose the left join -- AND a.end_date IS NULL -- AND lower(a.assign_type) = 'primary' ORDER BY a.user_id, p.patient_id| if ($dbtype ne "oracle"); Thanks much! Tom
Thomas Good wrote: > Hi, > > I am wondering if there is a way to set conditions on a left joined > table without hosing the join altogether: > > query = qq |SELECT p.*, a.user_id > FROM patient_dosing p > LEFT JOIN patient_assignment a > ON p.patient_id = a.patient_id > WHERE p.dose_type = 'Missed (AWOL)' > AND (p.dose_date >= $start_date AND p.dose_date <= $end_date) > -- the next two conditions hose the left join > -- AND a.end_date IS NULL > -- AND lower(a.assign_type) = 'primary' > ORDER BY a.user_id, p.patient_id| if ($dbtype ne "oracle"); What do you want to happen? What do you mean by a left-join where the right-hand side has assign_type='primary'? -- Richard Huxton Archonet Ltd