Re: joins - Mailing list 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.Elphick@lfix.co.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