CODEWITHSUNDEEP
phpjoinmatchingnames
blankspac3
blankspac3

Reputation: 3

Need to match user ids from two fields to their names in another table

I am working on some stuff for my game server and making a custom site ban.

I have a ban table (ban time, reason, banned player, banning admin, etc) The banned player and banning admin field both use player ids which are stored in the other table.

I know how to join tables but I can't figure out how to distinguish between the banned player and the admin, the tables are basically like:

info table:

some info field| more info | more info| player_id | creator_id

info info      | info info | info info|         1 |          2
info info      | info info | info info|         3 |          2
info info      | info info | info info|         5 |          4

players table:

id  | name

1   | john
2   | steve
3   | sally
4   | bob
5   | jack

The player id is the player that was banned and the creator is the admin, all names whether admin or player are all stored in the players table. What I want is to have a page that shows the banned player, the reason for ban, the banning admin and the time

Upvotes: 0

Views: 85

Answers (3)

Alain
Alain

Reputation: 36954

You may try :

SELECT
  info1,
  info2,
  p1.name AS player_name,
  p2.name AS admin_name
FROM more_infos mi
JOIN players p1 ON p1.id = mi.player_id
JOIN players p2 ON p2.id = mi.creator_id;

see Sql Fiddle with Demo.

Upvotes: 1

discomatt
discomatt

Reputation: 195

SELECT j1.name banned_player, j2.name admin
FROM info_table it
JOIN players j1 ON j1.id = it.player_id
JOIN players j2 ON j2.id = it.creator_id

Give that one a try.

Upvotes: 0

doublesharp
doublesharp

Reputation: 27599

You need to join to the players table twice - once for the banned user (player) and once for the creator of the ban (admin), like so:

SELECT info.*, player.name, admin.name
FROM info
JOIN players player ON player.id = info.player_id
JOIN players admin ON admin.id = info.creator_id

Upvotes: 1

Related Questions