Select acrosscross multiple tables

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Pieter Meiring
Your email address : pdm@shef.ac.uk

System Configuration
---------------------
Architecture (example: Intel Pentium) : P150/AMDK6

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.13

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-6.5.x

Compiler used (example: gcc 2.7.2) : RH and Mandrake binaries

Please enter a FULL description of your problem:
------------------------------------------------

Select on multiple tables fails.

For example. I have a database consisting of 3 tables:

patients (id name surname dob)
scans ( id patient_id area )
areas ( code description )

scans.patient.id relates to sacns.patient_id
areas.code relates to scans.area

even though correct entries are present in all tables, the query:

SELECT * FROM patients,scans,areas
WWHERE patients.surname='Smith'
and scans.patient_id=patients.id
and scans.area=areas.code ;

fails giving NO result despite there being 90 valid 'Smith' entries.

I have tried this select with several test databases and it always fails
where it tries to relate across 3 or more tables without a common key in
all tables Is this a feature or a bug?

The previous version of Postgres I used (6.3.2) allowed this select and
worked. MySQL allows it and works.

Pieter Meiring
pdm@shef.ac.uk