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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | HK | 2002-11-29 09:15:08 | Re: 'now' doesnt seem to work in stored procedure |
Previous Message | David Scullion | 2002-11-29 03:12:50 | [NOVICE} php compile |