How to join and filter with missing rows?

Question

I have a problem with creating a correct query, or with an incorrect schema of my database. I'm not sure how should I fix my problem.

My task is to return a list of questions and answers for all categories for a given user.

I'm using the query:

SELECT *
  FROM categories
  LEFT OUTER JOIN category_user_relations ON categories.id = category_user_relations.category_id
  LEFT OUTER JOIN questions ON categories.id = questions.category_id
  LEFT OUTER JOIN answers ON questions.id = answers.question_id
 WHERE category_user_relations.user_id = < user_id >
   AND answers.brief_id = < user_id >
    

And this works perfectly. But the problem starts when we have a missing entry in category_user_relations. Then joins are returning nothing, and we don't have all categories (and questions). This is an incorrect result because if the user didn't have an answer I still should return all questions from all categories.

So, my questions are:

• Should I create a better query that will be able to handle that scenario?
• Maybe query is ok, but I have to always create entries in category_user_realtions when I'm adding/removing users?

My db schema:

Category

id	category
1	Category_1
2	Category_2
3	Category_3

category_user_relations

id	category_id	user_id	note
1	1	5	txt1
2	2	3	txt2
3	3	2	txt3

category_question_relations

id	category_id	question
1	1	Q1_cat1
2	2	Q1_cat2
3	3	Q1_cat3

answers

id	question_id	answer
1	1	Ans1
2	2	Ans2
3	3	Ans3

Answer 1

As mustaccio mentions in the comments, you need to move the predicates from the WHERE clause to the joins:

SELECT
  *
FROM
  categories cat
LEFT JOIN
  category_user_relations cat_user
    ON cat_user.category_id = cat.id
        AND cat_user.user_id = < user_id >
LEFT JOIN
  questions qst
    ON qst.category_id = cat.id
LEFT JOIN 
  answers ans
    ON ans.question_id = qst.id
        AND ans.brief_id = < user_id >

Technically, you could add an OR to the WHERE clauses:

SELECT
  *
FROM
  categories cat
LEFT JOIN
  category_user_relations cat_user
    ON cat_user.category_id = cat.id
LEFT JOIN
  questions qst
    ON qst.category_id = cat.id
LEFT JOIN 
  answers ans
    ON ans.question_id = qst.id
WHERE
  (cat_user.user_id = < user_id > OR cat_user.user_id IS NULL)
   AND (ans.brief_id = < user_id > OR ans.brief_id IS NULL)

However this has the disadvantages of:

1. Lack of brevity
2. Takes context of join condition away from where you would look for it
3. Risks blowing up the query if you aren't careful with your parenthesis

Other notes not related to the question but worth mentioning:

1. Any join that is not an inner is an outer join, so while LEFT OUTER JOIN is valid, all left joins are outer joins so you can save yourself some typing.
2. It's perfectly fine to use the table name as the alias unless you're trying to reference the same table within a correlated subquery, in which case you'll have issues. As a matter of course I always specify the alias, even if it is the table name, just so people know I did choose the alias purposefully.
3. Because someone might read this in the future who doesn't already know, SELECT * is bad form - especially when multiple tables have identically named columns - i.e. id.