Link table Query?

[immanuelx2]

I am dealing with 3 tables: users, clubs, and users_clubs

 

Basically, users_clubs is the linking table (because the multiplicity is many to many both ways)

 

users:

id username
===========
1  Jack

 

clubs:

id clubname
===========
1  Cool Club
2  Sweet Club

 

users_clubs:

user_id club_id
===============
1       1
1       2

 

So my question is, how can i set up the MySQL query to retrieve all of the names of the Clubs that user id 1 is associated with? I came up with this so far but it's no worky

 

SELECT clubs.name
FROM clubs
WHERE users_clubs.user_id = $userid AND clubs.id = users_clubs.club_id

 

I have a feeling I need to use a LEFT or RIGHT join somewhere but I am not sure how to do that.

 

Thanks in advance and +Reps to all who help!

[fenway]

Your feeling is correct:

SELECT clubs.name
FROM users_clubs
INNER JOIN clubs ON ( clubs.id = users_clubs.club_id )
WHERE users_clubs.user_id = '$userid'