sql - 在同一张表上连接两次，在另一张表上连接一次时获取重复项(总共 3 个)

Question

我对 sql 比较陌生，并且一直在尝试让一个非常复杂的查询(对我来说)工作一段时间，但我一直在使用节点 postgres 在每个列中获取重复值。通过这个查询，我试图在仪表板上跟踪用户信息、计划信息和电子邮件信息。在我们开始查询之前，这里是表格 -

USER TABLE (u) - 跟踪用户信息

+----+-------+---------+-------------+----------+
| id | first |  last   |    email    | password | 
+----+-------+---------+-------------+----------+
|  1 | joe   | smith   | j@gmail.com |     1234 | 
|  2 | mary  | johnson | m@gmail.com |     3445 | 
|  3 | harry | gold    | h@gmail.com |     4345 | 
+----+-------+---------+-------------+----------+

计划表 (p) - 用户可以与 friend 制定的计划

+----+--------------+-----------+------------+------+--------+-----------+---------+------+
| id | experienceid | hostid(u) | guestid(u) | date |  time  | paidid(u) | groupid | newp |
+----+--------------+-----------+------------+------+--------+-----------+---------+------+
| 33 |            1 |         1 | [1,2,3]    | 4/20 | 8:00pm | [1,2]     |       1 | true |
+----+--------------+-----------+------------+------+--------+-----------+---------+------+

EMAIL TABLE (e) - 根据用户参与的计划跟踪我发送给用户的消息

+-------------+-----------+---------+----------+
|  email(u)   | planid(p) | confirm | reminder |
+-------------+-----------+---------+----------+
| j@gmail.com |        33 | null    | null     |
| m@gmail.com |        33 | true    | false    |
| h@gmail.com |        33 | true    | false    |
+-------------+-----------+---------+----------+

现在对于查询，我正在尝试做的是组合所有三个表以获得此输出 -

+-------+---------------+---------------------------+---------+---------+------------+---------------+---------------+
| id(p) | hostname(u+p) |      paidguests(u+p)      | time(p) | newp(p) | groupid(p) |  reminder(e)  |  confirm(e)   |
+-------+---------------+---------------------------+---------+---------+------------+---------------+---------------+
|    33 | joe smith     | [joe smith, mary johnson] | 8:00pm  | true    |          1 | [true, false] | [true, false] |
+-------+---------------+---------------------------+---------+---------+------------+---------------+---------------+

现在我在查询中停止的地方，我几乎让它工作了，但我一直在重复的值，看起来像这样 -

+-------+---------------+----------------------------------------------------+---------+---------+------------+----------------------------+---------------------------+
| id(p) | hostname(u+p) |                  paidguests(u+p)                   | time(p) | newp(p) | groupid(p) |        reminder(e)         |        confirm(e)         |
+-------+---------------+----------------------------------------------------+---------+---------+------------+----------------------------+---------------------------+
|    33 | joe smith     | [joe smith, mary johnson, joe smith, mary johnson] | 8:00pm  | true    |          1 | [true, false, true, false] | [true, false, true false] |
+-------+---------------+----------------------------------------------------+---------+---------+------------+----------------------------+---------------------------+ 

现在我真的不关心确认和提醒列相对于 paidguests(u+p) 列的顺序，只要正确的数据在那里并且没有重复。这是我目前的查询 -

SELECT p.id, 
       Concat(u.first, ' ', u.last)              AS hostname, 
       Array_agg(Concat(us.first, ' ', us.last)) AS paidguests, 
       p.time, 
       p.groupid, 
       p.newp, 
       Array_agg(e.confirm)                      AS confirm, 
       Array_agg(e.reminder)                     AS reminder 
FROM   plans p 
       CROSS JOIN Unnest(p.paidid) AS allguests 
       LEFT JOIN users us 
              ON allguests = us.id 
       LEFT JOIN emails e 
              ON p.id = e.planid 
       LEFT JOIN users u 
              ON p.hostid = u.id 
WHERE  p.experienceid = $1 
       AND p.date = $2 
GROUP  BY p.id, 
          u.first, 
          u.last, 
          p.paidid, 
          p.time, 
          p.groupid, 
          p.newp, 
          confirm, 
          reminder 
ORDER  BY Array_length(p.paidid, 1) DESC 

所以基本上只是想在没有重复的情况下得到正确的表格。在我将连接添加到电子邮件表之前它一直在工作，但不完全确定为什么会重复。

希望我的解释很透彻。如果没有，请让我知道我能澄清什么!非常感谢:)

Answer 1

尝试在 where 子句中添加此条件:

AND us.email = e.email

这里的罪魁祸首是 -> 所有电子邮件都与所有用户连接，而不管他们的电子邮件 ID，因为所有用户和所有电子邮件都具有相同的 plan_id。因此重复。