Search Postgresql Archives

left outer join on 3 tables ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

 

 

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux