INNER JOIN ? outcomes on two columns

Question

I'm a little bit confused about which join to apply and where.

I have a mysql database that does betting in an IRC client.

It stores usernames , their guess and the eventual outcome of the game they bet on

the outcomes_table is like this

+--------------+
| id   outcome |
+--------------+
| 1    win     |
| 2    lose    |
+--------------+

the user_table is like this

+----+----------+----------+-------------------+
| id | username | guess_id | bettingsession_id |
+----+----------+----------+-------------------+
|  1 | name1    |        1 |                 1 |
|  2 | name2    |        1 |                 2 |
|  3 | name3    |        2 |                 2 |
   4   name1             1                   2
+----+----------+----------+-------------------+

the betting_session_table is like this:

+----+---------+
| id | result  |
+----+---------+
|  1 |       1 |
|  2 |       2 |
+----+---------+

I want to get a list of the bets of a user with their guess joined to the outcome and the result joined to the

eg: select each row a different bet username, guess (win/lose), result (win/lose)

Something like:

SELECT *
FROM user_table
INNER JOIN betting_session_table ON bettingsession_id = betting_session_table.id
INNER JOIN outcomes_table ON guess_id = outcomes_table.id
INNER JOIN outcomes_table ON result = outcomes_table.id
WHERE username = 'name1'

However this doesn't work, not sure but I don't think it lets me join the outcomes_table.id twice to two different columns but I want to this because the user may bet 'win' but result 'lose' etc.

EG: I want to return

+----+----------+----------+----+---------+--------------------+----+--------+----+---------+
| id | username | guess_id | id | outcome | betting_session_id | id | result | id | outcome |
+----+----------+----------+----+---------+--------------------+----+--------+----+---------+
|  1 | name1    |        1 |  1 | win     |                  1 |  1 |      1 |  1 | win     |
|  4 | name1    |        1 |  1 | win     |                  2 |  2 |      2 |  2 | lose    |
+----+----------+----------+----+---------+--------------------+----+--------+----+---------+

EDIT:

In the end I used two separate alias for each join which seems to work; here is the code from the actual table that works rather than the cut down example above.

SELECT *
FROM `xcoins_betting_log` A
LEFT JOIN `xcoins_betting_session` B ON A.betting_session_id = B.id
LEFT JOIN `xcoins_common_tables`.`xcoins_betting_outcomes` C ON A.guess_id = C.id
LEFT JOIN `xcoins_common_tables`.`xcoins_betting_outcomes` D ON B.outcome_id = D.id
WHERE `user_id` =9

Answer 1

I'm not sure if this is what you want, but I hope so.

SELECT
    usr.*,
    res.outcome,
    IF(res.id = usr.guess_id, 'User win', 'User lose') AS result
FROM user_table AS usr
INNER JOIN betting_session_table AS bet ON
    bet.id = usr.bettingsession_id
INNER JOIN outcomes_table AS res ON
    res.id = bet.result
WHERE usr.username = 'name1'

---

Choose correct join

The most common joins is LEFT and INNER. Lets say the users have placed their bets, but the football game (or whatever) isn't completed yet, then you won't have the row in the outcomes_table right? The game isn't finished so the results will come later.

If you use INNER JOIN, the row in the outcomes_table won't match for unfinished games
--- INNER JOIN requires matches.

If you want to see the bets also before the game has started, you can use LEFT JOIN. LEFT JOIN won't remove rows that hasn't got any outcome, the users will still be listed
--- LEFT JOIN doesn't care.

INNER JOIN: Game must have result
LEFT JOIN: Game might have result