Results 1 to 7 of 7

Thread: Sql - Not In

  1. #1
    Join Date
    Oct 2003
    Posts
    22

    Unanswered: Sql - Not In

    Hi Guys,

    I have an easy SQL that i am having problems with, i want to select any tblCV.candID records where there is not a related CandID in tblCandidate, i have this, but it is wrong:

    SELECT [tblCV].[CandID]
    FROM tblCV, tblCandidate
    WHERE CLng([tblCV].[CandID]) Not In (SELECT tblCandidate.CandID FROM tblCandidate, tblCV WHERE tblCandidate.CandID = CLng(tblCV.CandID));


    Any ideas cheers si

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This should do it:

    SELECT [tblCV].[CandID]
    FROM tblCV LEFT JOIN tblCandidate ON tblCV.[candid]=tblCandidate.[candid]
    WHERE tblCandidate.[candid] Is Null
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    My first inclination is that you're looking fo r[tblCV].[CandID] to be both not in your subselect, and as a join criteria in your subselect.

    The easiest way to solve your problem is to use the Find Unmatched Query Wizard, then look at the SQL it generates. View --> SQL View from the query window
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Oct 2003
    Posts
    22
    Thanks RedNeckGeek,

    That was ideal, my brain isnt working at all today!

    Cheers Si


    Originally posted by RedNeckGeek
    This should do it:

    SELECT [tblCV].[CandID]
    FROM tblCV LEFT JOIN tblCandidate ON tblCV.[candid]=tblCandidate.[candid]
    WHERE tblCandidate.[candid] Is Null

  5. #5
    Join Date
    Oct 2003
    Posts
    22
    Actually, one more question..

    How can i use this to delete those records?

    Cheers Si

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Now's where you need to use the IN clause ...

    DELETE * FROM TBLCV
    WHERE TBLCV.[CANDID] IN (
    SELECT [tblCV].[CandID]
    FROM tblCV LEFT JOIN tblCandidate ON tblCV.[candid]=tblCandidate.[candid]
    WHERE tblCandidate.[candid] Is Null )
    Inspiration Through Fermentation

  7. #7
    Join Date
    Oct 2003
    Posts
    22
    Sorry about the delay in repling,

    Thanks that was ideal!!

    Cheers Si.

Posting Permissions

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