mysql查询用于从不同表中获取相关数据以用于建议的连接

Question

我们正在开发具有不同概念的类似社交网站的门户...但是为了进行建议的连接，我们在 mysql 查询中遇到了困难... 我们正在尝试为用户提供类似的数据...

SELECT u.* 
    FROM educonnect_user u 
    LEFT OUTER JOIN  educonnect_user_qualification q ON u.id = q.user_id 
    LEFT OUTER  JOIN educonnect_user_contact a1 ON u.id = a1.user_id 
WHERE q.type_of_institution in 
        (
            SELECT type_of_institution 
            FROM educonnect_user_qualification qi 
            WHERE qi.user_id = 3
         ) 
      AND q.college in 
         (
             select college 
             from educonnect_user_qualification qc 
             where qc.user_id = 3
          ) 
      AND q.country in 
          (
             select country 
             from educonnect_user_qualification qco 
             where qco.user_id = 3
          ) 
      AND a1.country in 
          (
             select country 
             from educonnect_user_contact cc 
             where cc.user_id = 3
          ) 
      AND a1.state in 
          (
             select state 
             from educonnect_user_contact cs 
             where cs.user_id = 3
          )

像这样我要加入 10 个表..但问题是无论我在哪里给出 AND 运算符都不会生成结果，如果我给出 OR 运算符它返回所有用户..这些运算符将给出我知道的输出的逻辑..但是对于这个问题，我需要有效工作的不同建议..否则可以使用任何其他特定运算符更改查询？ ？

Answer 1

您不需要所有这些 WHERE IN 条件。您只需要 where educonnect_user.user_id = 3，因为 educonnect_user 表与其他两个表 educonnect_user_qualification 和 educonnect_user_contact 连接>，因此连接将确保对于具有 id=3 的用户，字段：type_of_institution、college、 country,..（和其他字段）存在于同一用户的其他两个表中，但是您需要关心您需要什么类型的联接，具体取决于您想要从您的数据库中选择哪些字段表格是否左、右。所以我认为以下查询您正在寻找什么：

SELECT u.* 
FROM educonnect_user u 
LEFT OUTER JOIN  educonnect_user_qualification q ON u.id = q.user_id 
LEFT OUTER  JOIN educonnect_user_contact a1 ON u.id = a1.user_id 
where u.user_id = 3

Answer 2

希望这会有所帮助::

SELECT u.* 
    FROM educonnect_user u 
    LEFT OUTER JOIN  educonnect_user_qualification q ON u.id = q.user_id 
    LEFT OUTER  JOIN educonnect_user_contact a1 ON u.id = a1.user_id 
    left join educonnect_user_qualification qi on (q.type_of_institution=qi.type_of_institution and qi.user_id=3)
  left join educonnect_user_qualification qc  on (q.college=qc.college and qc.user_id=3)
  left join educonnect_user_qualification qco on (q.country=qco.country and qco.user_id=3)
  left join educonnect_user_contact cc on (a1.country=cc.country and cc.user_id=3)
  left join educonnect_user_contact cs on (a1.state=cs.state and cs.user_id=3)