#1
  1. No Profile Picture
    Certified Genius
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    over there
    Posts
    77
    Rep Power
    13

    joining tables across mulitiple columns


    If I have two tables (Members, Teams) with Teams having multiple foreign keys to the Members table how would go about selecting each team and getting the members names into the recordset as well. for example

    --- Members --__----------- Teams ---------
    ID_Name______ID_Member1_Member2
    1__Rob_______1__1_______2
    2__Tom_______2__3_______4
    3__Jim
    4__Dave


    And I wanna return from a query...
    TeamID__Member1__Member2
    1_______Rob______Tom
    2_______Jim_______Dave

    What combinations of joins, subqueries, etc would I have to use in order to get that result??? Thanks, rob
    Last edited by rob5408; July 10th, 2002 at 03:06 PM.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    12
    Is this your complete table structure? Just 2 members per team?
    - Walt

    Will code for food.
  4. #3
  5. No Profile Picture
    Certified Genius
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    over there
    Posts
    77
    Rep Power
    13
    well actually it is 4 members but just as an example i showed two
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    12
    The would be much easier if you had 3 tables.


    Members_Table
    MemID (Primary Key)
    FirstName
    LastName
    Address
    etc.

    Teams_Table
    TeamID (Primary Key)
    TeamName

    Roster_Table
    TeamID (Foreign Key)
    MemID (Foreign Key)

    You could then just select the MemID and TeamID from the Roster_Table. No joins. No subqueries. Just a simple select.
    - Walt

    Will code for food.

IMN logo majestic logo threadwatch logo seochat tools logo