joined tables

I am no database guru and I am also new to php/mysql

I want to be able to store data where one of the fields could have multiple
values which are stored in another table

eg. the first table is called family with a field called name which contains
family names
the second table is called members with a fiedl also called name which
contains first names
the third table is an intermediate table that only contains ids of the other
tables.
(the data is below)

By using inner joins I can get a list of all the people in the database (or
other variants eg. all the people with first names of Mary)

My first question is - is this the best way to do stuff like this?
My second question is - how do I insert new data? eg to insert Mary Stevens
(the first name Mary already exists)

I hope this all makes some sense

Thanks

Neil



table - family
id  name
1 jones
2 smith
3 mcdonald
4 talbot
5 jackson


table - members
id  name
1 john
2 mary
3 ted
4 sally
5 bob
6 jane


table - fammem
famid  memid
1 1
1 3
2 4
5 4
3 6
4 1


SELECT * FROM family inner join fammem on family.id = fammem.famid inner
join members on fammem.memid  = members.id
id  name  famid  memid  id  name
1 jones 1 1 1 john
1 jones 1 3 3 ted
2 smith 2 4 4 sally
3 mcdonald 3 6 6 jane
4 talbot 4 1 1 john
5 jackson 5 4 4 sally

1) No. You must add third column that unically describes a person (primary
key). E.g. there will more than one john smith.

2) And my first answer is the answer on your first question:

table - fammem
personid    famid  memid
1    1    1
2    1    3
3   32   4
4    5    4
5    3    6
6    4    1


sql:

insert into fammem( famid , memid) values (5, 1);
insert into fammem( famid , memid) values (1, 1);
insert into fammem( famid , memid) values (3, 3);
...

the personid    is autoincr. field.

3) You shouldn`t use INNER JOIN, you may use simple SELECT:

select fammem.*, family.name, members.name  from family , fammem, members
where  family.id = fammem.famid and   fammem.memid=members.id

4) uffff....

"Neil" <neil@jansons.net> сообщил/сообщила в новостях следующее:
news:20040824103144.62587.qmail@pb1.pair.com...
> I am no database guru and I am also new to php/mysql
>
> I want to be able to store data where one of the fields could have
multiple
> values which are stored in another table
>
> eg. the first table is called family with a field called name which
contains
> family names
> the second table is called members with a fiedl also called name which
> contains first names
> the third table is an intermediate table that only contains ids of the
other
> tables.
> (the data is below)
>
> By using inner joins I can get a list of all the people in the database
(or
> other variants eg. all the people with first names of Mary)
>
> My first question is - is this the best way to do stuff like this?
> My second question is - how do I insert new data? eg to insert Mary
Stevens
> (the first name Mary already exists)
>
> I hope this all makes some sense
>
> Thanks
>
> Neil
>
>
>
> table - family
>       id  name
>       1 jones
>       2 smith
>       3 mcdonald
>       4 talbot
>       5 jackson
>
>
> table - members
>       id  name
>       1 john
>       2 mary
>       3 ted
>       4 sally
>       5 bob
>       6 jane
>
>
> table - fammem
>       famid  memid
>       1 1
>       1 3
>       2 4
>       5 4
>       3 6
>       4 1
>
>
> SELECT * FROM family inner join fammem on family.id = fammem.famid inner
> join members on fammem.memid  = members.id
>       id  name  famid  memid  id  name
>       1 jones 1 1 1 john
>       1 jones 1 3 3 ted
>       2 smith 2 4 4 sally
>       3 mcdonald 3 6 6 jane
>       4 talbot 4 1 1 john
>       5 jackson 5 4 4 sally