convert query to use of joins

Question

How can I transform the following query to a query with joins?

I tried the second query, but that gave me ?doubled? and not plausible results.

Thank you in advance!

works (but awkward and not using joins):

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

doesn't work:

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

Seems you missed one condition: hole_scores.user_id = users.id which you should add as

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

Answer 2

You have mixed the order of some of the tables and forgot one of the conditions. Here, try this:

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 courses
  ON courses.id = score_cards.course_id
INNER JOIN holes
  ON holes.id = hole_scores.hole_id
INNER JOIN users
  ON users.id = score_card_user.user_id 
  and users.id = hole_scores.user_id     
ORDER BY score_cards.id ASC, user_name ASC, holes.sort ASC

One more thing: table alias are there to avoid long typing. It's pointless giving the same name to them. Normally people do something like

select 
    whatever
from 
    table as t,
    other_table as ot
where
    ot.t_id = t.id