Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8

    Angry MYSQL Many-to-Many relationship help

    Hi Folks

    Just a newbie looking for some help using MySQL verions 4.016. Here is a DB design with three tables: Members (members address_book), Clubs (list of clubs,addresses,etc) and Positions (list of positions). The relationships between the three tables are as follows:

    Club may have many Members...
    Members may belong to many Clubs...
    Members may have Many positions with different Clubs
    Clubs may have many Positions from many Members

    -------------------------
    CREATE TABLE members (
    memberid int(11) NOT NULL auto_increment,
    fname varchar(50) default NULL,
    lname varchar(50) default NULL,
    ....other fields ....
    PRIMARY KEY (id)
    ) TYPE=MyISAM;

    --------------------------------
    CREATE TABLE clubs (
    clubno int(12) NOT NULL default '0',
    name varchar(30) NOT NULL default '',
    contact varchar(30) NOT NULL default '',
    ....other fields .....
    PRIMARY KEY (regno)
    ) TYPE=MyISAM;

    --------------------------------

    CREATE TABLE positions (
    posid int(2) NOT NULL auto_increment,
    positiontitle varchar (50) not null,
    PRIMARY KEY (ID)
    );

    sample data for positions
    INSERT INTO position VALUES ('1', 'Chairperson');
    INSERT INTO position VALUES ('2', 'Vice Chair');
    INSERT INTO position VALUES ('3', 'Past Chair');
    INSERT INTO position VALUES ('4', 'Secretary');
    INSERT INTO position VALUES ('5', 'Treasurer');
    INSERT INTO position VALUES ('6', 'Executive Director');
    etc....

    ----------------------------------

    Do I need to create a fourth table to relate my three main tables.

    CREATE TABLE relation (
    memberid int(11) NOT NULL,
    clubno int(12) NOT NULL,
    posid int(2) default NULL,
    ) TYPE=MyISAM;

    I need to be able to select all the executive members for each club with their titles...

    Any help would be appreciated?

    Danny

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you need that 4th table

    by the way your PRIMARY KEY(xxx) clauses are all wrong, they appear to reference columns (e,g, ID) that you haven't declared in the table -- probably just typos, right?

    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8

    MYSQL Many-to-Many

    Yes... they were just typo's

    CREATE TABLE members (
    memberid int(11) NOT NULL auto_increment,
    ....other fields ....
    PRIMARY KEY (memberid)
    ) TYPE=MyISAM;

    --------------------------------
    CREATE TABLE clubs (
    clubno int(12) NOT NULL default '0',
    ....other fields .....
    PRIMARY KEY (clubno)
    ) TYPE=MyISAM;

    --------------------------------

    CREATE TABLE positions (
    posid int(2) NOT NULL auto_increment,
    positiontitle varchar (50) not null,
    PRIMARY KEY (posid)
    );

    I'm now having abit of problems with my Select statment to ouptut a list of all clubs, with members name & positions(title)... Any help on that would be appreciated.

    Thanks
    Danny

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's your query look like so far?

  5. #5
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8

    Thumbs up

    Originally posted by r937
    what's your query look like so far?
    This is what I have and it works...

    Code:
    select * from members m, clubs c, positions p, relation r 
    where
    m.memberid = r.memberid and
    c.clubno = r.clubno and
    p.posid =  r.posid
    order by c.clubno, p.posid;
    Danny

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks fine, except you will probably get more mileage out of using JOIN syntax instead of the table list syntax

    JOIN syntax allows you to isolate the join conditions to the two tables they pertain to
    Code:
    select * 
      from members m
    inner
      join relation r   
        on m.memberid = r.memberid
    inner
      join clubs c
        on r.clubno = c.clubno
    inner
      join positions p
        on r.posid = p.posid
    order
        by c.clubno
         , p.posid
    so, what problems were you having with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8
    Hi

    My select statement worked fine - I just thought there could be a better way of doing it! What is the benefit of using a join versus the way I did it.

    Thanks

    Danny

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what benefit? JOIN syntax allows you to isolate the join conditions to the two tables they pertain to

    in addition, once you are comfortable with JOIN syntax, it is a trivial matter to code outer joins, whereas with the table list syntax, it's a p.i.t.a. if not outright impossible

    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •