SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    NOT EXISTS query problem

    Hi All

    I am struggling with a query at the end of a long day!!!

    I have:

    Code:
    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:

    Code:
    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:

    Code:
    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I have done it... one needs to handle the table names:

    Code:
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •