Home>

[What I want to achieve]
If i have the following two tables, I want to extract the distribution rate of your friends in one row using your ID as a key.
For example, if ID 21 is specified, I would like to extract it as follows.
Even if I try many times by myself, I am struggling because I can not extract it well in one line.
If there is a good way, I would appreciate it if you could teach me.

Result I want (ID21)

Distribution rate of friend 1 Distribution rate of friend 2 Distribution rate of friends 3
2.6 3 (Null)

Existing table

Friends table (user)

Principal ID name Friend 1 ID (f1_id) Friend 2 ID (f2_id) Friend 3 ID (f3_id)
20 tanaka 1 2 3
twenty one yoshida Four Five (Null)

Friend distribution rate table (friend_rate)

Friend ID (id) Distribution rate (rate)
1 1.5
2 2
3 2.5
Four 2.6
Five 3

[Tried]

Create three join tables, a friend table and a friend distribution rate table, from each table.
Extract the distribution rate.
Result → For ID20, it went well, but when I tried for ID30 with null, nothing could be extracted.
(I'm planning to create join tables separately, but will the separate tables be joined and friend3 will not be able to extract because the result is null?)

SET @target_id = 21;

select friend1.rate as friend distribution 1, friend2.rate as friend distribution 2, friend3.rate as friend distribution 3 from
(select rate from friend_rate inner join user
on friend_rate.id = user.f1_id where userinfo.id = @target_id) as friend1,
(select rate from friend_rate inner join user
on friend_rate.id = user.f2_id where userinfo.id = @target_id) as friend2,
(select rate from friend_rate inner join user
on friend_rate.id = user.f3_id where userinfo.id = @target_id) as friend3;

  • Answer # 1

    I would write ↓ like this.

    SELECT U.id, U.name, FR1.rate, FR2.rate, FR3.rate
    FROM user U
      LEFT JOIN friend_rate FR1
        ON FR1.id = U.f1_id
      LEFT JOIN friend_rate FR2
        ON FR2.id = U.f2_id
      LEFT JOIN friend_rate FR3
        ON FR3.id = U.f3_id
    WHERE U.id = 21

    Does friend3 result in being unable to extract because the result is null?

    I agree.
    The structure of this query isSELECT ... FROM friend1, friend2, friend3It is. In this case, the Cartesian product of 3 tables is returned. Cartesian product is a combination of all rows in each table. If the number of lines is 3, 2, or 5, 30 lines will be returned. When ID = 21, the result is 0 lines because it is 1 line, 1 line, and 0 lines.