Get results with multiple criterias-CodePudding

Lets say I have these tables

I want to get all matches for a concrete matchday along with bets for each match if its made by a concrete user. Basically avoid getting results from other users but still get all matches for the matchday. I tried WHERE match.matchday = 1 AND user.userId = 1 but this gives only the results where both the matchday and the userId match, so if there is no bet on a match from the user for the matchday it is not added to the results

The result should be like

Also I am open for suggestions if this is a good way to get what I want, or I should just use multiple requests to get the data and manage it in the application

CodePudding user response：

There are multiple ways to achieve it with small tweaks.

Here are 2 working solutions.

SELECT * 
FROM match 
LEFT JOIN bet ON match.matchId = bet.matchId AND bet.userId = 1
LEFT JOIN user ON bet.userId = user.userId
WHERE match.matchday = 1

SELECT * 
FROM match 
LEFT JOIN bet ON match.matchId = bet.matchId
LEFT JOIN user ON bet.userId = user.userId
WHERE match.matchday = 1 AND (
    bet.userId = 1 OR bet.userId IS NULL
)