Thread: Outer Joins

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
valuebut 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
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


Best Regards,

Sharon Cowling

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
-----------------------------------------------------------------

Your join notation goes in the FROM clause.  There are
various choices in syntax: I would write it like this:

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 INNER JOIN (forest_permit t LEFT OUTER
JOIN permit_key k ON t.permit_id = k.permit_id) ON
p.person_id = t.person_id
WHERE p.lastname LIKE 'Bloggs%'
AND p.firstname LIKE 'Joe%'
ORDER BY t.issue_date;

This choice shows the influence of MS platforms, I
guess.

I don't believe the parentheses in the FROM are
necessary, but helpful for clarity.

Note I have inserted wildcard characters in your LIKE
terms.  You will need those, otherwise the LIKE will
be evaluated as an equality, which is probably not
what you want.  Just put the "%" at the beginning,
middle, end or wherever you need it.

See the documentation on SELECT (under "SQL Commands")
for more detail on joins.


--- Sharon Cowling <sharon.cowling@sslnz.com> 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
> 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
>
>
> Best Regards,
>
> Sharon Cowling
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com