Re: three table join

On Tue, 2004-11-02 at 05:49 -0800, Kumar S wrote:
> Dear Group,
> I have two tables 1. Contacts and 2. Experiment.
>
> I have another link table con_exp_link table.
>
> Contacts
> Con_id
> con_fname
> con_lname
> con_address
>
>
> Experiment
> exp_id
> exp_name
> exp_scientist
> exp_publication.
>
>
>
> con_exp_link
> con_exp_id

I guess con_exp_id is meant to be a primary key in this table; I believe
it should be unnecessary, since the primary key can be the combination
of the other two fields:

CREATE TABLE con_exp_link (
con_id some_type REFERENCES contacts (con_id),
exp_id some_type REFERENCES experiment (exp_id),
PRIMARY KEY (con_id, exp_id)
);

> con_id
> exp_id
>
>
> In this link table I have nothing but primary keys of
> experiment and contacts table.
>
>
> Now my question:
>
> How can I fill the link table. I am highly confused.

INSERT INTO con_exp_link SELECT c.con_id, e.exp_id FROM contacts AS c,
experiment AS e;

That implicit join will create the Cartesian product of contact and
experiment and put every possible combination into con_exp_link. If
that is too broad, you will have to constrain the join in some way so as
to produce only the legal combinations.

Alternatively, construct a 2 column flat file of legal combinations,
with the columns separated by tab, and use COPY to load con_exp_link
from it.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Fear none of those things which thou shalt suffer;
behold, the devil shall cast some of you into prison,
that ye may be tried; and ye shall have tribulation
ten days; be thou faithful unto death, and I will give
thee a crown of life." Revelation 2:10