NOT EXISTS query problem

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

Ok I have done it… one needs to handle the table names:

SELECT a.checkpoints_pk, a.checkpoint_name
FROM checkpoints as a, locations
WHERE a.location_fk = locations.location_pk
AND a.location_fk = '7'
AND NOT EXISTS
(
SELECT b.checkpoints_pk
	FROM checkpoints as b, checkpoint_to_routine
	WHERE checkpoint_to_routine.inspection_routine_fk = 72
	AND b.checkpoints_pk = checkpoint_to_routine.checkpoint_fk
	AND a.checkpoints_pk = b.checkpoints_pk
)

which now gives the correct result

Thanks anyway!! Someone will probably come back and say this is inefficient?