Re: three table join

On Nov 2, 2004, at 11:25 AM, Vishal Kashyap @ [Sai Hertz And Control
Systems] wrote:

> Dear Kumar
>
>
>> I have two tables 1. Contacts and 2. Experiment.
>> In this link table I have nothing but primary keys of
>> experiment and contacts table.
>
>
> Whats the relation between
>
> Contacts and Experiment table.
> If they relate to each other the link table can be filled (By filled I
> understand insert)
>
> But then if these both table are linked by some relation you wont
> require to insert the data in the link table.
> Just a join query would do that.
>
> Does anyone has better idea then plz to enlighten us.

Kumar,

I had the same confusion when starting to use SQL (of any kind, not
just postgres).  The links must be calculated and inserted by you.
There is no automatic way for postgres to determine what these links
should be.  That said, if you have two tables with primary ids, one
just needs to insert all of the pairs of unique ids into the link
table.  If your two primary tables were built first, you may have to
look up the primary key for each table and then do the insert I
mentioned.  You could write a postgresql function to do facilitate this
process or you could do it at the application or API level using perl
DBI or something like that.  As Vishal noted, depending on whether you
have a one-to-one, a one-to-many, or a many-to-many relationship
between the two primary tables, you may not need the link table.  I
think only in the many-to-many case is it actually necessary.

Sean