SQLite查询选择另一个表中不存在的所有记录 [英] SQLite query select all records that does not exist in another table

问题描述

我在尝试执行 SQLite 查询以获取其他表中不存在的记录时遇到一些问题.基本上我有两个数据库表:

I am having some problem when trying to perform a SQLite query to get the records which does not exist from another table. Basically I have two database tables:

我的练习表存储了所有可用的练习，而 bookedExercise 表存储了每个用户预订的练习.我想做的是，例如，如果该练习确实存在于 bookedExercise 中，则应将其过滤掉.

My exercise table stored all the exercises available whereas the bookedExercise table store the exercises booked by each users. What I am trying to do is, for example if the exercise does exist in the bookedExercise, it should be filtered out.

这是我使用的 SQLite 查询:

Here is the SQLite query which I used:

SELECT exercise.exerciseID, exercise.exerciseType, exercise.amout FROM exercise LEFT JOIN bookedExercise WHERE exercise.exerciseID = bookedExercise.exerciseID AND bookedExercise.exerciseID IS NULL

但是，它返回了我的空记录.有什么想法吗?

However, it returned me empty records. Any ideas?

提前致谢.

推荐答案

如果你不喜欢使用连接，你可以使用

If you're fine with not using joins you could use

SELECT * FROM exercise WHERE exerciseID not in (SELECT exerciseID FROM bookedExercise)

这篇关于SQLite查询选择另一个表中不存在的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！