Re: Problem with joins

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco