Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Lightbulb Unanswered: Complicated SELECT from multiple tables

    Hi Everyone,

    I'm new to the forum, looks like a great resource! Hopefully you all might be able to help me with the query I'm currently stumped on:

    I have two tables, that look something like this:

    Users Sample Data
    -----------------------------------------
    id 1
    firstname John
    lastname Smith
    activity1 24
    activity2 35

    Activities Sample Data
    -----------------------------------------
    id 24
    activityName Basketball

    I basically want a query that will run and give me the following:

    John Smith Basketball Baseball

    I would like the query to pull the actualy activity names, instead of just the id. I know I can do this with multiple queries, but on my report that would mean I'm making possibly hundreds of queries, instead of just one. I thought maybe I could use an INNER JOIN, but I'm still pretty much a novice with advanced queries, so I'm up for any suggestions. Thanks in advance!

    Cheers,

    Grant

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best advice i have for you is to change your table design slightly
    Code:
    CREATE TABLE Users
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , firstname  VARCHAR(99)
    , lastname   VARCHAR(99)
    );
    CREATE TABLE Activities
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , activityName VARCHAR(99)
    );
    CREATE TABLE UserActivities
    ( usr_id INTEGER NOT NULL 
    , act_id INTEGER NOT NULL 
    , PRIMARY KEY ( usr_id , act_id )
    );
    once you see how this works, i'm sure you will agree that it is more flexible and the queries are a lot simpler

    there is one row in UserActivities for each activity that every user has

    a given user will be in UserActivities multiple times (once for each of his activities), and a given activity will be in UserActivities multiple times (once for each user that has that activity), but the combination of usr_id and act_id values will be unique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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