Thread

Select acrosscross multiple tables

Pieter Meiring <pdm@cazal.ctanet.fr> — 1999-11-15T10:17:35Z

============================================================================
                        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