Thread: joins

joins

From

Michiel Lange

Date:

28 November 2002, 21:43:21

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

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

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.

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?

I'm reading O'Reilly's Practical PostgreSQL at the moment, so if you have
it you may also want to point at some chapter there that I should read
fifteen times over ;-)

Thanks in advance,
Michiel

Re: joins

From

Oliver Elphick

Date:

29 November 2002, 03:02:31

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

Attachment

signature.asc