CODEWITHSUNDEEP
mysqljoin
Max13
Max13

Reputation: 921

Get 2x same table's column 1 result

I have a users table and a friends table.

I want to get the friendships of 1 user, but getting both user info in results. For example, let's say I have these tables:

- users (_id_, username, firstname, lastname)
- friends (_#id1, #id2_)

Here are some data:

- users (1, foo, Foo, BAR)
        (2, john, John, DOE)
- friends (1, 2)

If I query fiendship WHERE id1 = 1 OR id2 = 1, I know that with a JOIN I can get for each row, the user data. But how to get in 1 result (1 row) both users data?

Upvotes: 0

Views: 119

Answers (2)

Arion
Arion

Reputation: 31239

SELECT
    u1.*,
    u2.*
FROM
    friends
    JOIN users AS u1
        ON friends._#id1=u1._id_
    JOIN users AS u2
        ON friends.#id2_=u2._id_

Upvotes: 1

Konerak
Konerak

Reputation: 39763

SELECT u1.*, u2.*
FROM users u1
JOIN friends f1
ON u1._ID_ = friends._#id1_
JOIN users u2
ON u2._ID_ = friends._#id2_

Each row will contain all the info from one user, and then all the info from the second user. You might want to replace that first JOIN friends f1 with LEFT OUTER JOIN friends f1 to also display a row for users without friends.

Careful: a user with multiple friends will show multiple rows. There is no automatic way in MySQL to pivot these rows to columns. You can however GROUP on the u1._ID_, and then use the GROUP_CONCAT function to display the friends information.

Upvotes: 1

Related Questions