Skip site navigation (1) Skip section navigation (2)

Select acrosscross multiple tables

From: Pieter Meiring <pdm(at)cazal(dot)ctanet(dot)fr>
To: bugs(at)postgresql(dot)org
Subject: Select acrosscross multiple tables
Date: 1999-11-15 10:17:35
Message-ID: Pine.BSF.4.10.9911151110480.24730-100000@cazal.ctanet.fr (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :       Pieter Meiring
Your email address      : pdm(at)shef(dot)ac(dot)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(at)shef(dot)ac(dot)uk


pgsql-bugs by date

Next:From: The Hermit HackerDate: 1999-11-15 16:40:16
Subject: Re: Select across multiple tables
Previous:From: Chaotic InceptionsDate: 1999-11-14 21:03:04
Subject: Arrays of composites, bug or usage?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group