SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Obsessive designer Infizi's Avatar
    Join Date
    May 2004
    Location
    North Pole
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Join Help :D:D

    Hey Guys,

    I have a DB with the tables Member, MemberClub and Club. I want to join Member and Club so i can get a list of members with what clubs they are in, using the linkages in the MemberClub Table (This is because of a many-to-many relationship).

    I understand i need to query Member and join it to MemberClub, then join that query to Club yes?

    Can someone provide some help?

    Member(ID, Name)
    MemberClub(ID, MemberID, ClubID)
    Club(ID, Name)

    Much appreciated guys!
    New Design - PHP Slowly flowing in!

    Infizi Studio's

  2. #2
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not overly familiar with the JOIN statement, but would something like this not work:

    PHP Code:
    $query "SELECT * from Member, MemberClub, Club WHERE Member.ID = MemberClub.MemberID AND MemberClub.ClubID = Club.ID"
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  3. #3
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On another note, why do you have a MemberClub table at all? It seems to me that its a useless middle-man, although maybe you're just showing the information we need to work with and it does serve a purpose.
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  4. #4
    Obsessive designer Infizi's Avatar
    Join Date
    May 2004
    Location
    North Pole
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol.

    The reason that table is there is because when I normalized my table I ended up with a many to many relationship between the two tables. Although this isnt really applicable in mysql, it eliminates the redundancy of storing member information
    New Design - PHP Slowly flowing in!

    Infizi Studio's

  5. #5
    SitePoint Addict
    Join Date
    Dec 2004
    Posts
    240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Completely not checked:
    Code:
    SELECT m.Name,
           c.Name
      FROM Member m
    LEFT OUTER
      JOIN MemberClub mc
        ON mc.MemberID=m.ID
    INNER 
      JOIN Club c
        ON c.ID=mc.ClubID
    Please notice, that the last inner join is supposed not to include the clubs with no members.

    Please check it. I do not have data to check it and do not have time now to create and fill in tables.

    Your approach with having the 3rd table for many-to-many relationship is correct and very common.

  6. #6
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your memberclub table doesnt need to have an ID field, it is essentially just a lookup table that links members to clubs.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  7. #7
    SitePoint Addict
    Join Date
    Dec 2004
    Posts
    240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mandes is absolutely right. (MemberID, ClubID) would be the composite Primary Key for MemberClub. I did not think about it ...


Bookmarks

Posting Permissions

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