Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Unanswered: Show all Except specific

    i want to select all those record except those record who have birthdate less then 4/13/1992 and relation= son
    i tried this
    SELECT * FROM Dependents WHERE code = 2
    AND NOT CONTAINS
    (SELECT *
    FROM Dependents
    WHERE BirthDate < '4/13/1992' AND relation= 'son')
    but not working


    plz help
    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What rows does that return
    Is anythjng wrong with that
    And if so what do you think is wrong with your sql

    What makes you believe you need sub select for essentially what is a very simple compound where clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi Oryazi

    Imho it will be faster if you kick off subquery.

    Code:
    SELECT * FROM Dependents WHERE code = 2
    AND relation != 'son'
    OR code = 2 AND relation = 'son' AND
    BirthDate >= '4/13/1992'
    Regards
    Mike

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    As soon as I use "OR" in SQL or any other programming language, I ALWAYS use parentheses. When I don't I get almost 100% guaranteed buggy code.

    Code:
    SELECT * 
    FROM Dependents as D
    WHERE code = 2
    	AND NOT (relation = 'son' AND
    		BirthDate < '1992-04-13'
    		)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2009
    Posts
    34
    thanks its work

Posting Permissions

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