Re: Select across multiple tables

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