pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJOIN condition confusion
2+ messages / 2 participants
[nested] [flat]
* JOIN condition confusion
@ 2005-10-31 18:28 Thomas Good <tomg@sqlclinic.net>
2005-11-04 08:12 ` Re: JOIN condition confusion Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Thomas Good @ 2005-10-31 18:28 UTC (permalink / raw)
To: pgsql-sql
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");
Thanks much!
Tom
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: JOIN condition confusion
2005-10-31 18:28 JOIN condition confusion Thomas Good <tomg@sqlclinic.net>
@ 2005-11-04 08:12 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-11-04 08:12 UTC (permalink / raw)
To: Thomas Good <tomg@sqlclinic.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-04 08:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-31 18:28 JOIN condition confusion Thomas Good <tomg@sqlclinic.net>
2005-11-04 08:12 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox