友好表的MYSQL JOIN查询

Question

我有以下两个表

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

我这样建立了一个连接查询：

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;

它看起来像这样：

Mike Fenway |1|3|

吉姆·莫里森|3|1|

问题是我如何显示朋友的名字，使结果如下所示：

迈克·芬威|1|吉姆·莫里森|3|

吉姆·莫里森|3|迈克·芬威|1|

Answer 1

您需要执行进一步的连接以获取好友数据。我使用LEFT JOIN总是退还账号，即使没有朋友也是如此。INNER JOIN将只返回有朋友的帐户，而忽略没有朋友的帐户。

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;

Answer 2

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