我敢肯定最终的答案会让我感到尴尬，但我一直在谷歌上搜索并用头撞墙，但无法弄清楚。
我有下面描述的四个表。我正在尝试获取显示与用户相关的所有产品（通过user_products表格）和所有类别的查询。
表定义
CREATE TEMPORARY TABLE categories (
id INT PRIMARY KEY NOT NULL
, cat_name TEXT
);
CREATE TEMPORARY TABLE products (
id INT PRIMARY KEY NOT NULL
, category_id INT
, prod_name TEXT
);
CREATE TEMPORARY TABLE users (
id INT PRIMARY KEY NOT NULL
, user_name TEXT
);
CREATE TEMPORARY TABLE user_products (
id INT PRIMARY KEY NOT NULL
, product_id INT
, user_id INT
, quantity INT
);
插入语句
INSERT INTO categories VALUES (1, 'cat1')
,(2, 'cat2')
,(3, 'cat3')
,(4, 'cat4');
INSERT INTO products VALUES (1, 1, 'prod1')
,(2, 1, 'prod2')
,(3, 2, 'prod3')
,(4, 3, 'prod4')
,(5, 4, 'prod4');
INSERT INTO users VALUES (1, 'user1')
,(2, 'user2')
,(3, 'user3');
INSERT INTO user_products VALUES (1, 1, 1, 4)
,(2, 2, 1, 3)
,(3, 3, 1, 3)
,(4, 2, 2, 3)
,(5, 2, 2, 2)
,(6, 5, 2, 2);
我已经尝试了几种选择，这确实感觉像是一个外部连接场景。最有前途但行不通的查询是：
SELECT up.*, p.*, c.*, u.*
FROM user_products AS up
JOIN users AS u ON u.id = up.user_id
JOIN products AS p ON p.id = up.product_id
RIGHT OUTER JOIN categories AS c ON c.id = p.category_id
WHERE u.id = 1 OR u.id IS NULL
以上查询的结果
id | product_id | user_id | quantity | id | category_id | prod_name | id | cat_name | id | user_name
----+------------+---------+----------+----+-------------+-----------+----+----------+----+-----------
1 | 1 | 1 | 4 | 1 | 1 | prod1 | 1 | cat1 | 1 | user1
2 | 2 | 1 | 3 | 2 | 1 | prod2 | 1 | cat1 | 1 | user1
3 | 3 | 1 | 3 | 3 | 2 | prod3 | 2 | cat2 | 1 | user1
| | | | | | | 3 | cat3 | |
它不起作用的原因是它无法从类别表中返回“cat4”。显然这是因为WHERE子句从结果集中选择它。我不知道如何获得一个结果集，其中包括所有类别 + 指定 user_products 表中有记录的产品和数量的详细信息。
查询应该
- 返回所有类别
- 如果用户拥有该产品，则返回为用户信息填充的数据（user_products 中存在一条记录）
- 不返回具有其他用户 ID 的行
