dalewright
asked on
Many to Many Table
Evening Guys.. I know i should be able to do this, but i am getting in a muddle a bit..
I have 2 tables... One to display Councillors the other to display the groups that they belong to... I have SQL 2000...
One councillor can belong to many groups, simple ok?? one group can have many councillors... I decided that i should have two tables
Councillor TBL
CouncillorID
CouncillorName
Group TBL
GroupID
GroupName
I then wrote the following sql statment
SELECT CouncillorGroup.GroupName, CouncillorName.CouncillorN ame
FROM CouncillorGroup, CouncillorName, MembersGroup
WHERE CouncillorGroup.GroupID = MembersGroup.MGroupID
AND CouncillorName.CouncillorI D = MembersGroup.MCouncillorID
to create a many to many table (i am not that used to using sql 2000, from a access backgroud :O/ )
When i parse the statement into the analyzer, its works, or should i say i get no errors, however when i look in the tbl MembersGroup, there is no records displayed... I would say this is right, because nothing is being inserted into that tbl...
SO what should i really be doing?? i.e i guess the statement is near correct, but how do i create the many to many table to show the records??? do i use analyzer?? i guess i am doing something wrong big time..
Thanks in advance
I have 2 tables... One to display Councillors the other to display the groups that they belong to... I have SQL 2000...
One councillor can belong to many groups, simple ok?? one group can have many councillors... I decided that i should have two tables
Councillor TBL
CouncillorID
CouncillorName
Group TBL
GroupID
GroupName
I then wrote the following sql statment
SELECT CouncillorGroup.GroupName,
FROM CouncillorGroup, CouncillorName, MembersGroup
WHERE CouncillorGroup.GroupID = MembersGroup.MGroupID
AND CouncillorName.CouncillorI
to create a many to many table (i am not that used to using sql 2000, from a access backgroud :O/ )
When i parse the statement into the analyzer, its works, or should i say i get no errors, however when i look in the tbl MembersGroup, there is no records displayed... I would say this is right, because nothing is being inserted into that tbl...
SO what should i really be doing?? i.e i guess the statement is near correct, but how do i create the many to many table to show the records??? do i use analyzer?? i guess i am doing something wrong big time..
Thanks in advance
You should add an intermediate table to match councillors and groups
CREATE TABLE CouncillorGroup (
CouncillorID int,
GroupID int
)
If you wanted all of the valid Councillor/Group combinations you would use a statememt like...
SELECT Councillor.Name, Group.Name
FROM Councillor
INNER JOIN CouncillorGroup
ON Councillor.CouncillorID = CouncillorGroup.Councillor ID
INNER JOIN Group
ON CouncillorGroup.GroupID = Group.GroupID
CREATE TABLE CouncillorGroup (
CouncillorID int,
GroupID int
)
If you wanted all of the valid Councillor/Group combinations you would use a statememt like...
SELECT Councillor.Name, Group.Name
FROM Councillor
INNER JOIN CouncillorGroup
ON Councillor.CouncillorID = CouncillorGroup.Councillor
INNER JOIN Group
ON CouncillorGroup.GroupID = Group.GroupID
This should do the trick from your 3 tables:
SELECT CouncillorGroup.GroupName, CouncillorName.CouncillorN ame
FROM CouncillorGroup INNER JOIN MembersGroup
ON CouncillorGroup.GroupID = MembersGroup.MGroupID
INNER JOIN CouncillorName
ON MembersGroup.MCoullorID = CouncillorName.CouncillorI D
SELECT CouncillorGroup.GroupName,
FROM CouncillorGroup INNER JOIN MembersGroup
ON CouncillorGroup.GroupID = MembersGroup.MGroupID
INNER JOIN CouncillorName
ON MembersGroup.MCoullorID = CouncillorName.CouncillorI
ASKER
Both of those are right, in fact even my own was correct... But how do i get the records to show in the third table, the new MembersGroup table??? i execute the query, and no rows are effected... I have a list of councillors, from 1 to 21 and a list of groups, 1 to 18... Both are automatically populated.... using auto increment... Any ideas..???
To populate your MembersGroup table with all combinations of your CouncillorName and CouncillorGroup, try this:
INSERT INTO MembersGroup (MGroupID, MCouncillorID)
SELECT CouncillorGroup.GroupID, CouncillorName.CouncillorI D
FROM CouncillorGroup, CouncillorName
INSERT INTO MembersGroup (MGroupID, MCouncillorID)
SELECT CouncillorGroup.GroupID, CouncillorName.CouncillorI
FROM CouncillorGroup, CouncillorName
ASKER
Right rafrancisco... good shout that works fine, however i now have a recordset of 1050 and each member belongs to every single group. For instance, 50 ppl belong to group Test when in fact there should only be 10..
so for instance...
user Bell only belongs to groups
test1
test2
test3
However user is in every group, this is the same for each user.. now i would assume this would happen, cause i need a FK on the Member tbl... But how would i go about this.. for instance, how would i say that user bell belongs to Group test1, 2 and 3 but not all off the other?? Man this is turning out to be more complicated than i thought..
Cheers
so for instance...
user Bell only belongs to groups
test1
test2
test3
However user is in every group, this is the same for each user.. now i would assume this would happen, cause i need a FK on the Member tbl... But how would i go about this.. for instance, how would i say that user bell belongs to Group test1, 2 and 3 but not all off the other?? Man this is turning out to be more complicated than i thought..
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER