mysql - 子查询总是返回单条记录

Question

我尝试合并查询 1 是查询 2 的子查询到 3。但它总是返回单个记录。以下是我的查询，

1.SELECT REPLACE(LEFT(friend_id, LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 

Output :
friend_id   
32,44

2.SELECT id, CONCAT(firstname," ",lastname) AS username FROM register WHERE id IN(32,44)

Output :
id  username    
32  Suresh M
44  Senthil Kumar

示例代码，

3.SELECT t1.id, CONCAT(t1.firstname," ",t1.lastname) AS username 
FROM register AS t1 
INNER JOIN friends_list AS t2 ON t1.id=t2.login_userid
WHERE t1.id IN( SELECT REPLACE(LEFT(friend_id, LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 )

Output :
id  username    
32  Suresh M

我想要结果，

id  username    
32  Suresh M
44  Senthil Kumar

请更正我的错误查询(第 3 次)。

Answer 1

只需删除您的内部连接条件

SELECT t1.id, CONCAT(t1.firstname," ",t1.lastname) AS username 
    FROM register AS t1 
    WHERE t1.id IN( SELECT REPLACE(LEFT(friend_id,      LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 )