mysql查询不到邮件_mysql – sql查询通过电子邮件获取用户列表

我的示例数据如下所示,我的问题是如何根据提供的输入电子邮件查找朋友的电子邮件

| users |

| user_id | email |

|---------|--------------------------|

| 1 | ashutosh8657@example.com |

| 2 | Kanchhi@example.com |

| 3 | modi@example.com |

| 4 | andy@example.com |

| 5 | maya@example.com |

| 6 | jetli@example.com |

| 7 | john@example.com |

| user_relations |

| user_relation_id | requestor_user_id | receiver_user_id | friend_status |

|------------------|-------------------|------------------|---------------|

| 1 | 2 | 4 | 1 |

| 2 | 2 | 6 | 1 |

| 3 | 2 | 7 | 1 |

| 4 | 5 | 2 | NULL |

| 5 | 5 | 7 | NULL |

| 6 | 7 | 2 | NULL |

| 7 | 7 | 4 | 1 |

| 8 | 7 | 5 | 1 |

| 9 | 7 | 6 | 1 |

| 10 | 4 | 2 | 1 |

| 11 | 4 | 3 | 1 |

| 12 | 4 | 5 | 1 |

| 13 | 4 | 6 | 1 |

| 14 | 4 | 7 | 1 |

例1：假设,如果我提供以下输入作为电子邮件：

john@example.com

然后我的预期输出应该是这个(顺序无关紧要)：

andy@example.com

jetli@example.com

Kanchhi@example.com

maya@example.com

这里,在user_relations表john@example.com中,其userId为7,对于userId 7,你可以看到朋友是UserIds 4,6,2,5,因此我需要4,6,2,5的电子邮件地址

如果friend_status的值为1,则只考虑朋友,除了1之外他们不是恶魔.我尝试过这个查询,但结果为零.请帮忙

SELECT

case when u.user_id = r.receiver_user_id then requestor.email else receiver.email end as friend_email

FROM users u

JOIN user_relations r

ON (r.requestor_user_id = u.user_id OR r.receiver_user_id = u.user_id)

AND r.friend_status = 1

LEFT JOIN users requestor ON requestor.user_id = r.requestor_user_id

LEFT JOIN users receiver ON receiver.user_id = r.receiver_user_id

WHERE u.email in ('john@example.com')

GROUP BY friend_email

HAVING COUNT(DISTINCT u.user_id) > 1