Problem with joins - Mailing list pgsql-novice

Hi,

I have a query which I need to check if a person has a forest associated with them.
Problem is that I need an outer join on the line that has: and t.permit_id = fk.permit_id as there may not be a
permit_idin the faps_key table.  The person id is set to 858 for testing but will be a variable in the application
code.

This is the original query that does not contain the outer join:

select
p.person_id,
p.type,
t.permit_id,
fp.location1,
fp.location2,
fp.location3,
fk.date_key_due
from person5 p,
faps_permit t,
faps_key fk,
forest_person3 fp
where p.person_id = fp.person_id
and t.permit_id = fk.permit_id
and p.person_id = 858
order by t.permit_id

This is my attempt:

select
p.person_id,
p.type,
t.permit_id,
fp.location1,
fp.location2,
fp.location3,
fk.date_key_due
from person5 p,
forest_person3 fp
INNER JOIN
(faps_permit t LEFT OUTER JOIN faps_key fk
ON t.permit_id = fk.permit_id)
ON p.person_id = t.person_id
where p.person_id = fp.person_id
and p.person_id = 858
order by t.permit_id

ERROR:  Relation 'p' does not exist

I'm doing something wrong obviously but I've managed to confuse myself!
Any hints, help?!  Below is the table structures.

Regards,

Sharon Cowling

taupo=> \d person5
                   Table "person5"
    Attribute     |         Type          | Modifier
------------------+-----------------------+----------
 person_id        | integer               | not null
 firstname        | character varying(25) | not null
 lastname         | character varying(25) | not null
 dob              | date                  | not null
 street           | character varying(50) | not null
 suburb           | character varying(50) |
 city             | character varying(50) | not null
 homephone        | character varying(15) |
 workphone        | character varying(15) |
 mobile           | character varying(15) |
 type             | character varying(30) | not null
 date_approved    | date                  | not null
 approved_by      | character varying(50) | not null
 vehicle_type     | character varying(50) |
 vehicle_rego     | character varying(6)  |
 drivers_licence  | character varying(10) |
 firearms_licence | character varying(20) |
 notes            | character varying(80) |
 status           | character varying(10) |
Indices: firstname_idx,
         fullname_idx,
         lastname_idx,
         person5_drivers_licence_key,
         person5_firearms_licence_key,
         person5_pkey


taupo=> \d forest_person3
            Table "forest_person3"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 person_id | integer               | not null
 location1 | character varying(30) |
 location2 | character varying(30) |
 location3 | character varying(30) |
Index: forest_person3_pkey


taupo=> \d faps_permit
                  Table "faps_permit"
     Attribute     |          Type          | Modifier
-------------------+------------------------+----------
 permit_id         | integer                | not null
 person_id         | integer                | not null
 date_from         | date                   | not null
 date_to           | date                   | not null
 location          | character varying(30)  | not null
 purpose           | character varying(30)  | not null
 subpurpose        | character varying(30)  | not null
 vehicle_rego      | character varying(6)   |
 vehicle_type      | character varying(30)  |
 dogs              | character varying(3)   |
 permit_conditions | character varying(200) |
 other_info        | character varying(100) |
 issued_by         | character varying(12)  | not null
 issue_date        | date                   |
 permit_printed    | integer                |
 firearms_licence  | character varying(20)  |
 drivers_licence   | character varying(10)  |
Index: faps_permit_pkey


taupo=> \d faps_key
                  Table "faps_key"
    Attribute    |         Type          | Modifier
-----------------+-----------------------+----------
 key_code        | character varying(6)  | not null
 date_key_issued | date                  |
 date_key_due    | date                  |
 key_issued_by   | character varying(12) |
 description     | character varying(20) |
 comments        | character varying(30) |
 permit_id       | integer               |
 status          | character varying(10) |
Index: faps_key_pkey