mysql - Sql查询错误，可能是语法错误

Question

我被查询困住了，我想检索关注者记录以及他关注的人，因为 following_label 可能是 1 或 0所以我为粉丝做了，效果很好，

SELECT firstname,lastname,type,image,username 
FROM relations
LEFT JOIN users ON relations.subscriber=users.id
WHERE relations.leader=9 
LIMIT 12

关系表结构是 id ,subscriber(follower) 和 leader(parent)

但现在我想检查他关注的同一条记录，即检查是否订阅者=领导者。

所以我尝试了这个，但不幸的是它似乎没有用。

SELECT firstname,lastname,type,image,username,subscriber as sub 
FROM relations
LEFT JOIN users ON relations.subscriber=users.id
WHERE relations.leader=9 limit 12 AND
    SELECT CASE 
        WHEN subsriber = 9 
           THEN 1 
        ELSE 0 
    END as Salable, * 
FROM relations

I Want to get something like this soundcloud.com/userid3218377271/followers if you check first three box they are my followers and if you mouse over them you see even i am following three of them and the last one m not following so "follow back" button exists in it

Answer 1

要获得一个用户正在关注谁以及谁在关注同一用户，正确的命令是 UNION 或 UNION ALL。

SELECT firstname,lastname,type,image,username,subscriber as sub 
FROM   relations
       LEFT JOIN users ON relations.subscriber=users.id
WHERE  relations.leader=9 limit 12
UNION ALL 
SELECT firstname,lastname,type,image,username,subscriber as sub
FROM   relations
       LEFT JOIN users ON relations.subscriber=users.id
WHERE  relations.subscriber=9 limit 12

与 UNION 或 UNION ALL 合并的查询需要具有相同的字段列表，或者至少，相同的字段计数和相同的字段类型相同的位置。

要了解谁在关注用户，如果用户在关注一个 LEFT JOIN 应该可以做到这一点

SELECT firstname, lastname, type, image, username
     , s.subscriber as sub
     , ISNULL(fb.leader) followback
FROM   relations s
       INNER JOIN users ON relations.subscriber = users.id
       LEFT  JOIN relations fb ON user.id = fb.leader AND fb.subscriber = 9
WHERE  s.leader=9 limit 12

如果 followback 为 1，则用户没有关注订阅者。您可以更改 followback 的公式 user 我使用了一个简单的，只是为了举个例子。