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);
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) |