mysql - 如何运行此MySQL JOIN查询？

假设我有两张桌子。
第一个表是角色列表。用户可以有许多角色。

mysql> select id, user_id, name from personas_personas;
+----+---------+--------------+
| id | user_id | name         |
+----+---------+--------------+
|  8 |       1 | startup      |
|  9 |       1 | nerd         |
| 10 |       1 | close        |
| 12 |       2 | Nerd         |
| 13 |       2 | Startup      |
| 14 |       2 | Photographer |
+----+---------+--------------+
6 rows in set (0.00 sec)

现在，我有另一个表，叫做“approvals”。

mysql> select id, from_user_id, to_user_id, persona_id  from friends_approvals;
+----+--------------+------------+------------+
| id | from_user_id | to_user_id | persona_id |
+----+--------------+------------+------------+
|  2 |            1 |          2 |          8 |
|  3 |            1 |          2 |          9 |
+----+--------------+------------+------------+
2 rows in set (0.00 sec)

如果from_user想要批准to_user到角色，则插入记录。
我正在试着做这个查询…
给定一个用户，找到它的所有角色。然后，对于每个角色，确定它是否被批准用于某个to_user。如果是，则在结果集中返回is_approved=1。否则，返回结果集中的is_approved=0。
所以我从这里开始：

SELECT *
FROM personas_personas
WHERE user_id = 1
LEFT JOIN friends_approvals ON
...but i don't know where to go from here.

因此，最终结果集应该包含personas_personas表中的所有列，然后每个结果也都包含is_approved列。

最佳答案

 SELECT
   pp.*,
   CASE
     WHEN exists (
         SELECT
          *
         FROM
          friends_approvals fa
         WHERE
          fa.from_user_id = pp.user_id AND
          fa.persona_id = pp.id AND
          fa.to_user_id = 2
          )
      THEN 1
      ELSE 0
   END as is_approved

 FROM
   personas_personas pp
 WHERE
   pp.user_id=1

或者，根据你的口味：

 SELECT
   pp.*,
   CASE
     WHEN fa.from_user_id IS NOT NULL
      THEN 1
      ELSE 0
   END as is_approved

 FROM
   personas_personas pp
     LEFT OUTER JOIN friends_approvals fa ON
       pp.user_id = fa.from_user_id AND
       pp.id = fa.persona_id AND
       fa.to_user_id = 2
 WHERE
   pp.user_id=1