MySQL Join

[guitarist809]

Yea, i can't do these for my life, if somebody could explain how to do one and how to get the data it returns. (what it would return in php using mysql_fetch_assoc()).

[fenway]

How about some same table structures?

[guitarist809]

Ok, i'll get the data, but there are like 9 tables to be linked together...

[guitarist809]

OK, here are the tables that I currently have:
[activities] - Contains all sorts of activities
+--------------+
activity_id - e.x., 1
category - climb
subcategory - climbing
summaryrpt - yes
abrev - clmb

[locations] - all sorts of locations in the world
+--------------+
location_id    //1
location  //Mount Everest
city  //N/A
state
country
notes //high mountian

[trips] - all tables link here, after here
+-------------+
trip_id //1
location_id //1 (links to table location, location would be Mount Everest)
trip_date // day trip was on

[trips_activities] - activities completed by users in database
+-------------+
activity_id    //id for index
trip_id //links to trip table
act_id  //links to activities table
user_id    //links to users (table specified below)

[trips_comments] - comments left by each user on each trip
+-------------+
comment_id //index
trip_id  //links to trip
user_id  //links to users (table will be specified blow)
comment //the comment by that user

[trips_participants]
+----------------+
participant_id  //index
trip_id  //links to trip
user_id  //links to the users table which will be specified next

[users]
+-------------+
user_id //index
username  //username for login
name //users name
password //users password
config_sys //permissions
config_trip  //permissions

Wow, there's like 4 more tables, but i think i can figure it out if i see an example.

This is what im trying to do:

Grab the trips in order by date ASC, then get all the users who went on the trip (table: trips_participants), then grab the comments left by those users(table: trips_comments), then grab all activities for each user (table trips_activities), then get all the names of the activities (table: activities), then get the location of the trip (table: trips), then get the name of the location (table: locations).

Is this even possible with a join?

Thanks,
Matt :D

[hvle]

is this your homework?

[guitarist809]

no...

Its so I can keep track of the activities that i do...

[hvle]

first of all, I think the database design you had is well structured and binded.  Good design.
You do not need more than 3 joins level to get what you wanted.

Here is 1 example of using join, there are many type of join: left join, right join and join.  So, I'm not sure about restriction of each table.



This is one example:
This query return a user's name, user's comment, trip activities and trip id:

[code]
"select users.username as p_username, trips_comments.comment as p_comment, trips_activities.activity_id as p_activity_id,
trips_comments.trip_id as p_trip_id from users join trip_comments on users.user_id = trips_comments.user_id join trips_activities
on users.user_id = trip_activities.user_id;
[/code]
I wrote this merely by looking at table structure.  If it doesn't work, post both query and error.
if you need a simple example on a specific thing, let me know also.

[fenway]

Why have different trips tables?

[guitarist809]

Sorry, I STILL have not had time to test this out :( (been busy).

I was just wondering, are joins significantly faster then doing the 10 query "SELECT this FROM table WHERE it='that'"? (the page took 15 minutes to load that way)...

Just wondering

[fenway]

That's too general a question... you can have very fast joins, and very fast selects...