CODEWITHSUNDEEP
phpmysqljoin
shiva8
shiva8

Reputation: 2162

MYSQL JOIN QUERY for friendship table

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

Answers (2)

John Woo
John Woo

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

Fenton
Fenton

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

Related Questions