Richard Downs Richard Downs - 1 month ago 4
MySQL Question

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

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

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'