sql - LEFT OUTER JOIN 查询不包含左表的结果
问题描述
我有一些桌子:
CREATE TABLE IF NOT EXISTS
days(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
active BOOLEAN
);
INSERT into days (name, active) VALUES ('S', True);
INSERT into days (name, active) VALUES ('M', True);
INSERT into days (name, active) VALUES ('T', True);
INSERT into days (name, active) VALUES ('W', True);
INSERT into days (name, active) VALUES ('Th', True);
INSERT into days (name, active) VALUES ('Fr', True);
INSERT into days (name, active) VALUES ('Sut', True);
CREATE TABLE IF NOT EXISTS
user(
id INTEGER PRIMARY KEY AUTOINCREMENT,
mail TEXT,
nickname TEXT,
isadmin INTEGER
);
INSERT into user (nickname, mail) VALUES ('overblown', 'abc@abc.com');
CREATE TABLE IF NOT EXISTS
dish(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
price FLOAT
);
INSERT into dish (name,price) VALUES ('Pizza', 40);
INSERT into dish (name,price) VALUES ('Soup', 20);
INSERT into dish (name,price) VALUES ('Bread', 40);
INSERT into dish (name,price) VALUES ('Chips', 42);
CREATE TABLE IF NOT EXISTS
menu(
id INTEGER PRIMARY KEY AUTOINCREMENT,
dish_id INTEGER,
day_id INTEGER
);
INSERT into menu (dish_id, day_id) VALUES (1, 1);
INSERT into menu (dish_id, day_id) VALUES (2, 1);
INSERT into menu (dish_id, day_id) VALUES (3, 1);
INSERT into menu (dish_id, day_id) VALUES (4, 1);
CREATE TABLE IF NOT EXISTS
request(
id INTEGER PRIMARY KEY AUTOINCREMENT,
menu_id INTEGER,
user_id INTEGER,
count FLOAT
);
INSERT into request (menu_id, user_id, count) VALUES (1, 1, 0.5);
INSERT into request (menu_id, user_id, count) VALUES (2, 1, 0.5);
我想从request
表中为某人用户获取所有记录,但还要将表中的所有记录添加到menu
某天,以便结果相对于menu_id
. Sqlite 不支持RIGHT OUTER JOIN
,我尝试使用UNION
:
select user.nickname as nick, dish.name as dish, days.name as day, request.count as size from request
INNER JOIN user on user.id = request.user_id
INNER JOIN menu on menu.id = request.menu_id
INNER JOIN dish on dish.id = menu.dish_id
INNER JOIN days on days.id = menu.day_id
where user_id=1 and days.id=1
UNION
SELECT null as nick, dish.name as dish, days.name as day, 0.0 size from menu
INNER JOIN dish on dish.id = menu.dish_id
INNER JOIN days on days.id = menu.day_id
where menu.day_id=1;
看起来不错,但结果没有唯一值。好的,我尝试使用LEFT OUTER JOIN
(相对于操作员改变桌子的侧面)
SELECT user.nickname as nick, dish.name as dish, days.name as day, count
FROM menu
LEFT outer JOIN request ON request.menu_id=menu.id
INNER JOIN user on user.id = request.user_id
INNER JOIN dish on dish.id = menu.dish_id
INNER JOIN days on days.id = menu.day_id
where menu.day_id=1 and request.user_id=1
但结果仅具有来自request
表的行,而不包含来自menu
. 我做错了什么?
预期结果:
('overblown', 'Pizza', 'S', 0.5)
('overblown', 'Soup', 'S', 0.5)
('overblown', 'Bread', 'S', NULL)
('overblown', 'Chips', 'S', NULL)
解决方案
您需要在left join
整个查询中使用:
SELECT u.nickname as nick, d.name as dish, dy.name as day, count
FROM request r LEFT JOIN
menu m
ON r.menu_id = m.menu_id AND
m.day_id = 1 LEFT JOIN
user u
ON u.id = r.user_id LEFT JOIN
dish d
ON d.id = m.dish_id LEFT JOIN
days dy
ON dy.id = m.day_id
WHERE r.user_id = 1;
推荐阅读
- reactjs - GitHub Pages 上的 React 应用程序 - URL 缺少存储库名称
- arangodb - ArangoDB:执行成本在执行计划中代表什么
- javascript - 如何通过jquery将字符串“1M,3F,2M”过滤为“1,3,2”?
- mysql - 为什么年龄过滤器不使用 laravel 中的 sql 查询过滤年龄?
- python - RetryError: 使用 Gcloud 调用 functools.partial 时超过了 600.0s 的最后期限
- json - Jackson JsonMappingException:无限递归(StackOverflowError)
- ruby - 使用 capybara + selenium 验证特定 css 值的文本
- python - 如何将输入、变量和文本从一个 python 脚本发送到另一个已经运行的 python 脚本?
- ponylang - 小马的静态类方法?
- list - Prolog - 计算两个列表中的匹配项