Thread: three table join
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 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. Can any one help me please. thanks kumar. __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com
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. -- With Best Regards, Vishal Kashyap. Did you know SaiPACS is one and only PACS Management tool. http://saihertz.com
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
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
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
On Nov 2, 2004, at 12:56 PM, Kumar S wrote: > 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 > Kuman, This implies that you will have a row in the chip table for every con_exp row, so you wouldn't need the con_exp table at all? Probably what you mean is that for every con_exp row, there are foreign keys referencing the primary keys in your chip and contacts tables? That gives you the many-to-many relationship and guarantees that you have an entry in both the contacts and chip tables for every row in the con_exp table. Sean