joins

From: Michiel Lange <michiel(at)minas(dot)demon(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: joins
Date: 2002-11-28 23:41:20
Message-ID: 5.1.0.14.0.20021129002205.02570048@192.168.1.3
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

  • Re: joins at 2002-11-29 05:02:33 from Oliver Elphick

Browse pgsql-novice by date

  From Date Subject
Next Message David Scullion 2002-11-29 03:12:50 [NOVICE} php compile
Previous Message Tom Lane 2002-11-28 22:32:21 Re: user defined function