• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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.CouncillorName
FROM CouncillorGroup, CouncillorName, MembersGroup
WHERE CouncillorGroup.GroupID = MembersGroup.MGroupID
AND CouncillorName.CouncillorID = 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

0
dalewright
Asked:
dalewright
  • 3
  • 3
1 Solution
 
dalewrightAuthor Commented:
Or should i say i have 3 TABLES... 1 for councillor, 1 for Group and the other as the MANY to Many tbl
0
 
Brian CroweDatabase AdministratorCommented:
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.CouncillorID
INNER JOIN Group
     ON CouncillorGroup.GroupID = Group.GroupID
0
 
rafranciscoCommented:
This should do the trick from your 3 tables:

SELECT CouncillorGroup.GroupName, CouncillorName.CouncillorName
FROM CouncillorGroup INNER JOIN MembersGroup
ON CouncillorGroup.GroupID = MembersGroup.MGroupID
INNER JOIN CouncillorName
ON MembersGroup.MCoullorID = CouncillorName.CouncillorID
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dalewrightAuthor Commented:
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..???
0
 
rafranciscoCommented:
To populate your MembersGroup table with all combinations of your CouncillorName and CouncillorGroup, try this:

INSERT INTO MembersGroup (MGroupID, MCouncillorID)
SELECT CouncillorGroup.GroupID, CouncillorName.CouncillorID
FROM CouncillorGroup, CouncillorName
0
 
dalewrightAuthor Commented:
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
0
 
rafranciscoCommented:
Create a stored procedure that will assign each councilor to the corresponding group:

CREATE PROCEDURE AddCouncillorToGroup ( @GroupName VARCHAR(100), @CouncillorName VARCHAR(100))
AS
INSERT INTO MembersGroup (MGroupID, MCouncillorID)
SELECT CouncillorGroup.GroupID, CouncillorName.CouncillorID
FROM CouncillorGroup, CouncillorName
WHERE CouncillorGroup.GroupName = @GroupName AND
CouncillorName.CouncillorName = @CouncillorName
GO

EXEC AddCouncillorToGroup ('test1', 'Bell')
EXEC AddCouncillorToGroup ('test2', 'Bell')
EXEC AddCouncillorToGroup ('test3', 'Bell')

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now