left outer join on 3 tables ?

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: left outer join on 3 tables ?
Date: 2008-09-16 02:28:06
Message-ID: 0836165E8EE50F40A3DD8F0D871372670110E91D@azsmsx421.amr.corp.intel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here is a simple example of what I need to do.....

Table: EMPLOYEES

name job

--------- -----------

alan welding

sue foreman

fred machinist

Table: WAGE

job rate

----------- -----------

welding 20

machinist 23

Table: DANGER

job level

---------- ----------

welding 2

machinist 3

foreman 1

OK, I want a query that'll give me the employee, their rate and their
job's danger level for jobs with danger level <= 2. I want one ine per
employee (regardless of the fact that there is no record in WAGE for sue
the foreman). So it looks like a left outer join is needed. This query
doesn't crash, but it leaves sue the foreman out...

select

e.name, w.rate, d.level

from

employees e left outer join wage w on (e.job = w.job),

danger d

where

d.job = w.job and

d.level <= 2;

Any suggestions? (Thanks in Advance !)

For testing, yoou can use these...

create table employees (name varchar(16), job varchar(32));

insert into employees (name,job) values ('alan','welding');

insert into employees (name,job) values ('sue','foreman');

insert into employees (name,job) values ('fred','machinist');

create table wage (job varchar(43), rate integer);

insert into wage (job, rate) values ('welding',20);

insert into wage (job, rate) values ('machinist',23);

create table danger (job varchar(32), level integer);

insert into danger (job, level) values ('welding',2);

insert into danger (job, level) values ('machinist',3);

insert into danger (job, level) values ('foreman',1);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Nolan 2008-09-16 02:52:23 Re: left outer join on 3 tables ?
Previous Message Tom Lane 2008-09-16 02:19:20 Re: Obfuscated stored procedures (was Re: Oracle and Postgresql)