Обсуждение: Problem with joins
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
Sharon,
> Thanks for your reply, the database could perhaps be improved, but
> the way in which the client wants the data stored and retrieved is a
> little different than what I have done before, the also require a
> lot of archiving, I'm not sure what you were referring to but if you
> mean that I am storing what appears to be duplicates of vehicle_rego
> and firearms_rego, these are actually different, a person can have a
> personal vehicle that is part of their details, but when they are
> issued a permit for a forest they may be using a different vehicle
> for this purpose, it might not even be theirs so this needs to be
> recorded in the faps_permit table. There are other instances where
> things like this occur in their business logic. I perhaps should
> name what appear to be duplicate columns different names, but I
> don't tend to like to do that.
Obviously, I can't make any reccomendations since I don't know the full
business logic. Anytime I see a database which has more than 40
tables or many tables with more than 16 columns, I immediately start
looking for ways to simplyfy things.
I'd recommend that you buy Joe Celko's "SQL for Smarties" and Fabian
Pascal's "Practical Issues in Database Management" to help you
discover the simplest possible expression for a complex set of
business rules.
> You were right about me confusing myself! Now to my lack of join
> experience (I found it easier in Oracle (+) !)
Ah, but PostgreSQL supports more kinds of Joins than Oracle does! AND
we're more SQL92-compliant. The Oracle left join syntax is
proprietary to Oracle.
> I changed the query but am getting the following error:
>
> select
> p.person_id,
> p.type,
> t.permit_id,
> fp.location1,
> fp.location2,
> fp.location3,
> fk.date_key_due
> FROM person5 p JOIN forest_person3 fp ON (p.person_id)
Close: FROM person5 p JOIN forest_person3 fp ON (p.person_id =
fp.person_id)
> 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 = 858
> order by t.permit_id
Got it now? You can't mix explicit JOINs (e.g. TableA JOIN TableB ON
Col1 = Col2) with implicit joins (FROM TableA, TableB WHERE col1 =
col2).
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes: > [ mostly good advice ] > ... You can't mix explicit JOINs (e.g. TableA JOIN TableB ON > Col1 = Col2) with implicit joins (FROM TableA, TableB WHERE col1 = > col2). Sure you can, though the explicit joins will be done first. See http://developer.postgresql.org/docs/postgres/explicit-joins.html regards, tom lane