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 (
$emailI 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 Source
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'