使用加入或别名获取具有相同报告电子邮件地址的用户的详细信息

Question

我有两个表

tbl_user：

id fisrtname lastname
5   John      Doe
6   Peter     Parker
7   Will      Smith

tbl_experience：

exp_id usr_id user_reporting_to
1       5     [e-mail] 
2       6     [e-mail]
3       7     [e-mail]

我想获取那些具有相同报告电子邮件地址的记录，其中条件 usr_id=5，因为当我登录到我的页面时，它会创建用户 ID 的会话，例如它现在是 5 在条件中使用加入或别名

SELECT texperience.tbl_experience_report_to_email AS tbl_experience_report_to_email, 
tuser.tbl_user_fname AS tbl_user_fname, 
texperience.tbl_experience_designation AS tbl_experience_designation 
FROM tbl_experience AS texperience,
tbl_user AS tuser 
WHERE tuser.tbl_user_fname = tuser.tbl_experience_id 
AND texperience.tbl_experience_report_to_email = texperience.tbl_experience_id
AND texperience.tbl_experience_user_id = 1

Answer 1

SELECT tbl_user.*, tbl_experience.user_reporting_to FROM tbl_user 
LEFT JOIN tbl_experience ON (tbl_user.id=tbl_experience.usr_id) 
LEFT JOIN (SELECT count(*) as total_user, user_reporting_to FROM tbl_experience GROUP BY user_reporting_to) as email_group ON 
(email_group.user_reporting_to = tbl_experience.user_reporting_to) WHERE email_group.total_user > 1

基本上有一个子查询将所有电子邮件地址分组，连接的表将返回那些在tbl_experience中多次出现电子邮件的用户。