Complicated SELECT from multiple tables

grantsewell · #1 (**permalink**) 03-07-09, 18:56

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

r937 · #2 (**permalink**) 03-07-09, 21:26

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

Internet Services

Web Development

Digital Marketing

Consumer Tech