Mysql – SQL SELECT name by id

joinmysqlselectsql

I need help with a sql query.

I have these 2 tables:

player_locations:

ID |  playerid  | location <- unqiue key
---|-----------------------
 1 |    1       | DOWNTOWN

and users:

ID  | playername | [..]
----|--------------------
 1  | example1   | ...

I need a select to get the users.playername from the player_locations.playerid. I have the unique location to get the player_locations.playerid.

Pseudo query:

SELECT playername 
FROM users
WHERE id = player_locations.playerid 
  AND player_locations.location = "DOWNTOWN";

The output should be example1.

Best Solution

This is just a simple INNER JOIN. The general syntax for a JOIN is:

SELECT stuff
FROM table1
JOIN table2 ON table1.relatedColumn = table2.relatedColumn

In your case, you can relate the two tables using the id column from users and playerid column from player_locations. You can also include your 'DOWNTOWN' requirement in the JOIN statement. Try this:

SELECT u.playername
FROM users u
JOIN player_locations pl ON pl.playerid = u.id AND pl.location = 'DOWNTOWN';

EDIT

While I personally prefer the above syntax, I would like you to be aware of another way to write this which is similar to what you have now.

You can also select from multiple tables by using a comma in your FROM clause to separate them. Then, in your WHERE clause you can insert your conditions:

SELECT u.playername
FROM users u, player_locations pl
WHERE u.id = pl.playerid AND pl.location = 'DOWNTOWN';