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@lfix.co.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