manyTomany Relationship  
Author Message
VBSam





PostPosted: Tue Jan 06 19:30:30 CST 2004 Top

SQL Server Developer >> manyTomany Relationship

Hi,

How do I set up a many to many relationship between two tables

do I need an intersection table? what are the ways of doing it.

Thanks in advance.

SA

SQL Server189  
 
 
Trey





PostPosted: Tue Jan 06 19:30:30 CST 2004 Top

SQL Server Developer >> manyTomany Relationship yep, you need an linking table.

at minimum, it needs the pk columns from tables1 and 2 [or rowid's if pk's
are wider than you'd like to propagate]

e.g.

create table example1 (ex1id int primary key, someCol1 varchar(20))
create table example2 (ex2id int primary key, someCol2 varchar(20))
create table link1to2 (ex1id int references example1(ex1id),
ex2id int references example2(ex2id),
primary key (ex1id, ex2id))

insert into example1 values (1, 'value 1.1')
insert into example1 values (2, 'value 1.2')
insert into example1 values (3, 'value 1.3')

insert into example2 values (4, 'value 2.1')
insert into example2 values (5, 'value 2.2')
insert into example2 values (6, 'value 2.3')

insert into link1to2 values (1, 4)
insert into link1to2 values (1, 6)
insert into link1to2 values (2, 4)
insert into link1to2 values (2, 5)
insert into link1to2 values (3, 5)
insert into link1to2 values (3, 6)

-- then select by joining the three together like so
select ex1.someCol1, ex2.someCol2
from example1 ex1
join link1to2 lk on ex1.ex1id=lk.ex1id
join example2 ex2 on lk.ex2id =ex2.ex2id





> Hi,
>
> How do I set up a many to many relationship between two tables
>
> do I need an intersection table? what are the ways of doing it.
>
> Thanks in advance.
>
> SA
>
>


 
 
CSharp





PostPosted: Tue Jan 06 20:13:08 CST 2004 Top

SQL Server Developer >> manyTomany Relationship Thank you very much, Trey, that was quick




> yep, you need an linking table.
>
> at minimum, it needs the pk columns from tables1 and 2 [or rowid's if pk's
> are wider than you'd like to propagate]
>
> e.g.
>
> create table example1 (ex1id int primary key, someCol1 varchar(20))
> create table example2 (ex2id int primary key, someCol2 varchar(20))
> create table link1to2 (ex1id int references example1(ex1id),
> ex2id int references example2(ex2id),
> primary key (ex1id, ex2id))
>
> insert into example1 values (1, 'value 1.1')
> insert into example1 values (2, 'value 1.2')
> insert into example1 values (3, 'value 1.3')
>
> insert into example2 values (4, 'value 2.1')
> insert into example2 values (5, 'value 2.2')
> insert into example2 values (6, 'value 2.3')
>
> insert into link1to2 values (1, 4)
> insert into link1to2 values (1, 6)
> insert into link1to2 values (2, 4)
> insert into link1to2 values (2, 5)
> insert into link1to2 values (3, 5)
> insert into link1to2 values (3, 6)
>
> -- then select by joining the three together like so
> select ex1.someCol1, ex2.someCol2
> from example1 ex1
> join link1to2 lk on ex1.ex1id=lk.ex1id
> join example2 ex2 on lk.ex2id =ex2.ex2id
>
>
>


> > Hi,
> >
> > How do I set up a many to many relationship between two tables
> >
> > do I need an intersection table? what are the ways of doing it.
> >
> > Thanks in advance.
> >
> > SA
> >
> >
>
>