Which approach should I use for retrieving details from this multi-table SQL instruction?

Question

I have 3 tables.

users

id | user_email | user_password | user_name

communities

id | name | code | admin

community_players

community_id | player_id

A user is logged into my app with their email address (

$email

).

I want to use that email address to look up which communities they are apart of.

My logic current logic is:

SELECT id FROM table users WHERE user_email = '$email' (call this new variable 'A')



SELECT community_id FROM table community_players WHERE player_id = 'A' (call this new variable 'B')



SELECT name FROM table communities WHERE id = 'B'

What approach should I take:

1) Come up with a single MySQL Statement that accomplishes the above in one go and returns an array of community names.

or

2) Run 3 separate MySQL statements each returning a variable in PHP to use in the next SQL statement (like the logic I used above)

or some other method?

Answer 1

TRY this in single query using JOIN docs mysql Join

SELECT communities.name 
FROM users 
join community_players 
on community_players.player_id=users.id 
join communities 
on communities.id=community_players.community_id   
WHERE users.user_email = '$email'

Which approach should I use for retrieving details from this multi-table SQL instruction?

Sign up