Reputation: 2162
I have the following two tables
CREATE TABLE accounts (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE friends (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
account_id INTEGER UNSIGNED NOT NULL,
friend_id INTEGER UNSIGNED NOT NULL,
created_on TIMESTAMP NOT NULL
);
I established a JOIN query as such:
SELECT accounts.name, accounts.id, friends.account_id FROM accounts
RIGHT JOIN friends ON accounts.id = friends.account_id
ORDER BY accounts.name LIMIT 10;
It will look something like:
Mike Fenway | 1 | 3 |
Jim Morison | 3 | 1 |
The question is how do I display the friends name so that the result appear as such:
Mike Fenway | 1 | Jim Morison | 3 |
Jim Morison | 3 | Mike Fenway | 1 |
Upvotes: 0
Views: 162
Reputation: 263943
SELECT
MainAccount.Name as OwnerName,
MainAccount.ID as OwnerID,
COALESCE(AnotherMain.Name, '') as FriendName
COALESCE(Friends.ID, '') as FriendID
FROM
Accounts as MainAccount
LEFT JOIN Friends ON
MainAccount.ID = Friends.ID
LEFT JOIN Accounts as AnotherMain ON
Friends.ID = AnotherMain.ID
ORDER BY MainAccount.Name LIMIT 10
Upvotes: 0
Reputation: 251292
You need to perform a further join to get the friend data. I have used LEFT JOIN
to always return the account, even if there are no friends. An INNER JOIN
would return only accounts that have friends and omit those which do not.
SELECT A.id, A.name, ACC.id `friendId`, ACC.name `friendName`
FROM accounts A
LEFT JOIN friends F ON A.id = F.account_id
LEFT JOIN accounts ACC ON F.friend_id = ACC.id
ORDER BY A.name LIMIT 10;
Upvotes: 2