mysql - 将查询转换为使用连接

Question

如何将以下查询转换为带有联接的查询？

我尝试了第二个查询，但这给了我“双倍”的结果？并且结果不合理。

提前谢谢您!

有效(但很尴尬并且不使用连接):

SELECT score_cards.id, score_cards.start_time, score_cards.end_time, users.name AS user_name, holes.sort, hole_scores.score 
FROM
score_card_user AS score_card_user,
score_cards AS score_cards,
score_card_hole_score AS score_card_hole_score,
hole_scores AS hole_scores,
holes AS holes,
courses AS courses,
users AS users
WHERE
score_card_user.sc_id = score_cards.id
AND score_card_hole_score.sc_id = score_cards.id
AND score_card_hole_score.hs_id = hole_scores.id
AND score_cards.course_id = courses.id
AND hole_scores.user_id = users.id
AND holes.id = hole_scores.hole_id
AND score_card_user.user_id = users.id
ORDER BY score_cards.id ASC, user_name ASC, holes.sort ASC

不起作用:

SELECT score_cards.id, score_cards.start_time, score_cards.end_time, users.name AS user_name, holes.sort, hole_scores.score
FROM
score_cards
INNER JOIN score_card_user
  ON score_cards.id = score_card_user.sc_id
INNER JOIN score_card_hole_score
  ON score_cards.id = score_card_hole_score.sc_id
INNER JOIN hole_scores
  ON hole_scores.id = score_card_hole_score.hs_id
INNER JOIN holes
  ON holes.id = hole_scores.hole_id
INNER JOIN courses
  ON courses.id = score_cards.course_id
INNER JOIN users
  ON users.id = score_card_user.user_id
ORDER BY score_cards.id ASC, user_name ASC, holes.sort ASC

Answer 1

您似乎错过了一个条件:hole_scores.user_id = users.id，您应该将其添加为

...
INNER JOIN users
  ON users.id = score_card_user.user_id AND hole_scores.user_id = users.id
....