Hi All
I am struggling with a query at the end of a long day!!!
I have:
SELECT checkpoints.checkpoints_pk, checkpoints.checkpoint_name
FROM checkpoints, locations
WHERE checkpoints.location_fk = locations.location_pk
AND checkpoints.location_fk = '7'
Gives 6 results of
“checkpoints_pk” “checkpoint_name”
“1” “Cooker 1”
“2” “Fridge 1”
“3” “External Door”
“4” “Food Shelf 1”
“5” “Gents Toilets”
“6” “Ladies Toilets”
I then have query:
SELECT checkpoints.checkpoints_pk, checkpoints.checkpoint_name
FROM checkpoints, checkpoint_to_routine
WHERE checkpoint_to_routine.inspection_routine_fk = 72
AND checkpoints.checkpoints_pk = checkpoint_to_routine.checkpoint_fk
AND checkpoints.checkpoints_pk = checkpoints.checkpoints_pk
That gives me the checkpoints for that routine and results in:
“checkpoints_pk” “checkpoint_name”
“2” “Fridge 1”
“3” “External Door”
“4” “Food Shelf 1”
“5” “Gents Toilets”
I want to have a query that finds the difference i.e. that in this case gives me:
“checkpoints_pk” “checkpoint_name”
“1” “Cooker 1”
“6” “Ladies Toilets”
I am trying to use AND NOT EXISTS with a sub-query but I am not getting it quite right somewhere
I have:
SELECT checkpoints.checkpoints_pk, checkpoints.checkpoint_name
FROM checkpoints, locations
WHERE checkpoints.location_fk = locations.location_pk
AND checkpoints.location_fk = '7'
AND NOT EXISTS
(
SELECT checkpoints.checkpoints_pk, checkpoints.checkpoint_name
FROM checkpoints, checkpoint_to_routine
WHERE checkpoint_to_routine.inspection_routine_fk = 72
AND checkpoints.checkpoints_pk = checkpoint_to_routine.checkpoint_fk
AND checkpoints.checkpoints_pk = checkpoints.checkpoints_pk
)
but that gives me an empty recordset, what am I missing?
Any help from anyone would be really appreciated