Re: Outer Joins

On Mar 13 Nov 2001 01:43, Sharon Cowling wrote:
> I come from an Oracle background and have noted that postgres 7.1 supports
> outer joins...but I'm not sure of the syntax.  Note below in the first AND
> clause the (+) next to k.permit_id, I need to get the nulls back as well as
> the value but I get an error when I use (+)
>
> SELECT t.permit_id, t.issue_date, t.issued_by, t.location, t.purpose ||'
> '|| t.subpurpose as spurpose, t.date_from, t.date_to, t.permit_conditions,
> t.other_info, k.key_code, p.person_id, p.firstname ||' '|| p.lastname as
> name FROM person p, forest_permit t, permit_key k

FROM erson p, (forest_permit t {LEFT|RIGHT} OUTER JOIN permit_key k ON
(t.permit_id = k.permit_id))

Change that, and eliminate the WHERE cluse with th + sign.

> WHERE p.person_id = t.person_id
> AND t.permit_id = k.permit_id(+)
> AND p.lastname LIKE 'Bloggs'
> AND p.firstname LIKE 'Joe'
> ORDER BY t.issue_date


Saludos... :-)

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------