Many to Many Table

Posted on 2005-04-18
Last Modified: 2010-03-19
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


Group TBL


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

Question by:dalewright
    LVL 1

    Author Comment

    Or should i say i have 3 TABLES... 1 for councillor, 1 for Group and the other as the MANY to Many tbl
    LVL 34

    Expert Comment

    by: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
    LVL 28

    Expert Comment

    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
    LVL 1

    Author Comment

    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..???
    LVL 28

    Expert Comment

    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
    LVL 1

    Author Comment

    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


    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..

    LVL 28

    Accepted Solution

    Create a stored procedure that will assign each councilor to the corresponding group:

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

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


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now