Re: Outer Joins

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