sql - SQL query to select all tuples that do not have a specific attribute at all
问题描述
I have a table (shown below) that maps users to the fruits that they like. This table is a result of a a complicated SELECT
statement, and now I am left with specifying the WHERE
condition.
I would like to select all users that do not like the fruit "Orange" at all.
U.id | F.fruit |
---|---|
1 | Apple |
1 | Orange |
1 | Orange |
2 | Mango |
3 | Mango |
4 | Mango |
4 | Grapes |
5 | Apple |
5 | Orange |
6 | NULL |
The resulting table should consist of users 2, 3, 4, 6
, because users 1
and 5
have tuples that map them to "Oranges".
I am not allowed to use any sub-queries (make another SELECT
statement). I am not sure how to do this given these restrictions, and I was not sure of how to google search for SQL queries for this specific situation.
解决方案
I would like to select all users that do not like the fruit "Orange" at all.
Assuming you have a table of users, I would suggest not exists
:
select u.*
from users u
where not exists (select 1
from <your query here> q
where q.user_id = u.user_id and q.fruit = 'orange'
);
Alternatively, you could aggregate, but this would only return users in your query:
select user_id
from <your query here>
group by user_id
having count(*) filter (where fruit = 'orange') = 0;
Or, if you showed the query, there might be other solutions as well.
推荐阅读
- heroku - 如何让我的 nextjs 应用程序和我的strapi CMS 具有相同的域和子域?
- python - 使用外部脚本写入 Django sqlite 数据库
- fullcalendar - FullCalendar:还有 viewRender 事件挂钩吗?
- maven - 无法将 Maven jar 部署到 Azure DO 存储库
- python - 是否有用于解析 AWS cron 字符串并将其转换为日历日期的 python 库?
- mysql - 从 1 个表中选择 2 的总和,但 1 个表具有 AND 条件
- git - 如何“git pull”留下未完成的分支
- keycloak - Keycloak 的 SAML 单次注销
- r - 计算每列高于 R 中阈值范围的值的数量
- javascript - 如何在信息窗口谷歌地图中添加画廊脚本