Link to home
Start Free TrialLog in
Avatar of dalewright
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.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

Avatar of dalewright
dalewright

ASKER

Or should i say i have 3 TABLES... 1 for councillor, 1 for Group and the other as the MANY to Many tbl
Avatar of Brian Crowe
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
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
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.CouncillorID
FROM CouncillorGroup, CouncillorName
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
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial