Thread

Re: Select across multiple tables

Marc G. Fournier <scrappy@hub.org> — 1999-11-15T16:40:16Z

You say it fails...what sort of error message are you getting, if any?

On Mon, 15 Nov 1999, Pieter Meiring wrote:

> 
> I appologise for sending you this directly, Marc. I tried the
> bugs@postgresql address but was bounced.
> This is a problem that arose recently on upgrading from 6.4 to 6.5
> Is it a feature or a bug?
> 
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
> 
> 
> Your name               :       Pieter Meiring
> Your email address      : pdm@shef.ac.uk
> 
> 
> System Configuration
> ---------------------
>   Architecture (example: Intel Pentium)         : Pentium and AMDK6
> 
>   Operating System (example: Linux 2.0.26 ELF)  : Linux 2.2.13
> 
>   PostgreSQL version (example: PostgreSQL-6.3.2)  :   PostgreSQL-6.5.2/3
> 
>   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
> WHERE     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
> Medical Imaging
> pdm@shef.ac.uk
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org