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

Re: Select across multiple tables

From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: Pieter Meiring <pdm(at)cazal(dot)ctanet(dot)fr>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Select across multiple tables
Date: 1999-11-15 16:40:16
Message-ID: Pine.BSF.4.10.9911151239520.75865-100000@thelab.hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)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(at)shef(dot)ac(dot)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(at)shef(dot)ac(dot)uk
> 
> 

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


pgsql-bugs by date

Next:From: J. RoeleveldDate: 1999-11-16 09:27:36
Subject: Problem with CREATE RULE <something> ON DELETE (PostgreSQL only executes the first expression)
Previous:From: Pieter MeiringDate: 1999-11-15 10:17:35
Subject: Select acrosscross multiple tables

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