Re: Problem with joins - Mailing list pgsql-novice

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