MySQL联接基于2个不同的表匹配值的另一个表值?

预期的结果是12(价格)和pic.png(配置文件图片)。是否有可能基于匹配的job_id(职位+要约)在职位和要约之间具有匹配的用户ID(全部基于$ _SESSION [user_id])来加入职位和个人资料?

       Jobs                          Offers                Profile
user_id | job_id | price |    | user_id | job_id |   |user_id | profilepic |
   1    |   1    |   12  |    |    4    |    1   |   |   4    |   pic.png  |
--------------------------    --------------------   -----------------------
$sql="SELECT
      j.price,
      p.profilepic
      FROM Jobs j
      INNER JOIN Offers o  ON o.job_id = j.job_id
      INNER JOIN Profile p ON o.user_id = p.user_id
      WHERE user_id=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i",$_SESSION['user_id']);
$stmt->execute();
$stmt->bind_result($price,$profilepic);
$stmt->fetch();

它不起作用,可能吗?

评论
  • 自惭形秽、
    自惭形秽、 回复

    You seem to be missing a join condition on offer :

    SELECT j.price, p.profilepic
    FROM Jobs j
    INNER JOIN Offers o  ON o.job_id = j.job_id AND o.user_id = j.user_id
    INNER JOIN Profile p ON o.user_id = p.user_id
    WHERE user_id = ?
    

    Actually, it is unclear why you need table offer in the query. This would produce the result that you expect:

    SELECT j.price, p.profilepic
    FROM Jobs j
    INNER JOIN Profile p ON p.user_id = j.user_id
    WHERE user_id = ?