CODEWITHSUNDEEP
mysqlsqlselectjoin
magl1te
magl1te

Reputation: 119

SQL SELECT name by id

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.

Upvotes: 3

Views: 77195

Answers (3)

AdamMc331
AdamMc331

Reputation: 16691

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

Upvotes: 9

Gally
Gally

Reputation: 73

I have a idea, try this:

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

Upvotes: 2

jewelhuq
jewelhuq

Reputation: 1203

Here is the solution.

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

Upvotes: 3

Related Questions