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

Re: joins

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Michiel Lange <michiel(at)minas(dot)demon(dot)nl>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: joins
Date: 2002-11-29 05:02:33
Message-ID: 1038546152.1383.381.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2002-11-28 at 23:41, Michiel Lange wrote:
> Joins never was my strongest point:
> 
> If I have these tables
> 
> CREATE TABLE Company(
> 	cmp_id serial PRIMARY KEY NOT NULL, -- I know NOT NULL is overkill...
> 	cmp_name varchar(30)
> );
> 
> CREATE TABLE Workers(
> 	wrk_id	serial PRIMARY KEY NOT NULL,
> 	wrk_name varchar(30) NOT NULL,
> 	wrk_company1 int4 NOT NULL REFERENCES Company.cmp_id,
> 	wrk_company2 int4 REFERENCES Company.cmp_id
> );

Your REFERENCES syntax is wrong.  It should be:

          ... REFERENCES Company (cmp_id)


> And I want to show all workers with their company names. As the tables 
> suggest each Worker works at at least one company, but possibly at two... I 
> think I set up the tables right (at least I hope so, but I have a strong 
> feeling it does)
> 
> How would I set up the SELECT.
> 
> I think up this (on the fly, not the slightest idea if it would work or not):
> SELECT wrk_id,wrk_name,cmp_name,cmp_name FROM ( Company INNER JOIN Company 
> ON (wrk_company1 = Company.cmp_id) very much clueless here...)

Since you are naming the same table in two different joins, you have to
name it twice with different aliases:

SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name
  FROM workers AS w
       LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id,
       company AS c1
 WHERE w.wrk_company1 = c1.cmp_id;

or

SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name
  FROM workers AS w
       INNER JOIN company AS c1 ON w.wrk_company1 = c1.cmp_id
       LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id;


(NB: your mixed-case names for tables are folded to lower-case, since
you didn't quote them in the CREATE TABLE statements.)

> I would go for an outer join for the second company they may work for, as 
> this value may be NULL, and an inner join would cause unwanted results, for 
> it would only display those workers that work for two companies.

This is a LEFT [OUTER] JOIB, 

> And how would it work if there was a third table involved, let's say 
> Car_brands (brand_id serial, brand_name varchar(30)). And the Worker has at 
> least one car, but at most two (find such an employer that wants to store 
> THAT :P)
> so that would make for two more fields in the Workers table:
> car_brand1 int4 NOT NULL REFERENCES Car_brands.brand_id, -- every worker 
> has at least one car...
> car_brand2 int4 REFERENCES Car_brands.brand_id

> Of course I would want to display the brand-names as well, and there is one 
> inner join and an outer join, that much I could figure out... (an outer 
> join, since the second car MAY be NULL...) how would this SELECT look like?

It's exactly the same:

SELECT w.wrk_id, w.wrk_name, c1.cmp_name, c2.cmp_name
  FROM workers AS w
       INNER JOIN company AS c1 ON w.wrk_company1 = c1.cmp_id
       LEFT JOIN company AS c2 ON w.wrk_company2 = c2.cmp_id
       INNER JOIN car_brands AS b1 ON w.car_brand1 = c1.brand_id
       LEFT JOIN car_brands AS c2 ON w.car_brand2 = c2.brand_id;

provided that it is true that every worker has at least one car, which
sounds unlikely unless having a car is a condition of employment.

-- 
Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Who shall ascend into the hill of the LORD? or who 
      shall stand in his holy place? He that hath clean 
      hands, and a pure heart..."            Psalms 24:3,4 

In response to

  • joins at 2002-11-28 23:41:20 from Michiel Lange

pgsql-novice by date

Next:From: HKDate: 2002-11-29 09:15:08
Subject: Re: 'now' doesnt seem to work in stored procedure
Previous:From: David ScullionDate: 2002-11-29 03:12:50
Subject: [NOVICE} php compile

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