Re: three table join - Mailing list pgsql-novice

Hello Oliver,
 Thank you for your mail with suggestions. I decided
to keep the con_exp_id as primary key because of the
fact that a contact can do as many experiments as he
can and one experiment can be done many contacts.
Thus these two tables share a Many to Many
relationship.


Thus for other tables con_exp_id will be made as
Foreign key .

For example:

Table: Chip_table
chip_id (PK)
con_exp_id (FK)
......
.....

Here it is easy for me to anchor con_exp_id as FK from
con_exp_link table.

Any other better alternative?

Thanks

Kumar




--- Oliver Elphick <olly@lfix.co.uk> wrote:

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




__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com