CODEWITHSUNDEEP
phpmysqlsql
user2123244
user2123244

Reputation: 99

Displaying name of two users from a different table

Keeping this clean and to the point:

I have two MySQL tables, users (id, nick) and bans (id, banned, bannedBy, length). I want to display in a table a list of bans, but instead of displaying the banned ID and the bannedBy ID, I want to display their nick. I can use an JOIN to get the nick of one of them, in something like this:

SELECT bans.id,bans.banned,bans.bannedBy,bans.length,users.nick
FROM bans
JOIN users ON users.id=bans.banned

But then I can't get the bannedBy's nick, and vice verca.

I hope I was clear, thanks in advance for any help.

Upvotes: 1

Views: 66

Answers (2)

Matten
Matten

Reputation: 17631

You can use two joins:

SELECT bans.id,bans.banned,b.nick as bannedBy,bans.length,u.nick
FROM bans
JOIN users u ON u.users.id=bans.banned
JOIN users b ON b.users.id=bans.bannedBy

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You have to join users table twice, on different keys.

SELECT
    bans.id,
    bans.banned,
    bans.bannedBy,
    bans.length,
    u.nick as 'banedNick',
    u2.nick as 'bannedByNick'
FROM
    bans
JOIN 
    users u ON users.id = bans.banned
JOIN
    users u2 ON users.id = bans.bannedBy

Upvotes: 2

Related Questions