20 Cents 20 Cents - 5 months ago 30
SQL Question

SQLite query select all records that does not exist in another table

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:

Database table

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.

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?

Thanks in advance.


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

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