SQL SELECT name by id

Question

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.

Solution 1

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';

Solution 2

Here is the solution.

SELECT
playername
FROM users
WHERE id = (SELECT id FROM player_locations WHERE location='DOWNTOWN')

Solution 3

I have a idea, try this:

SELECT playername 
FROM users
WHERE id IN (SELECT DISTINCT playerid FROM player_location WHERE location LIKE "DOWNTOWN");

Comments

[Iłya Bursov]

FROM users should be FROM users, player_locations

[AdamMc331]

SQL is never easy in the beginning. Keep working hard, and you'll solve problems like these in no time after a while. Glad I could help.