Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: Opposite sql selects?

    I need to write sql to filter out certain rows, and then get 'all the others' not including the rows in the first select.

    Problem is; they don't add up to the total number of rows. So what am I doing wriong?

    select count(*) from struct
    16019 rows

    select count(*) from struct where (structure_name='$R' and depth='6')
    7587 rows

    Then the 'all the others'
    select count(*) from struct where (structure_name!='$R' and depth!='6')
    6726 rows

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Then the 'all the others'
    >select count(*) from struct where (structure_name!='$R' and depth!='6')
    WRONG!

    select count(*) from struct where (structure_name!='$R' OR depth!='6')
    might give you the answer you expect
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2006
    Posts
    56
    Thanks, that worked

    But it does not seem logical to me. When you have two conditions that both need to be met to fit the criteria (using and 'and'), won't an 'or' break that? The 'or' then saying that either condition meets the criteria.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This is Boolean Algebra 101 basics.
    I am not about to provide you a rehash of a freshman college course.
    The flaw in what you did was immediately obvious to me when I 1st looked at the problem statement.

    The solution could also have been obtained by using "MINUS"; if you know how to properly use it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    name        number
    curly         1
    larry         1 
    moe           1
    shemp         2 
    joe           3
    curly joe     4
    
    where name  = 'curly' and number = 1   -- 1 result
    
    where name <> 'curly' and number <> 1  -- 3 results
    
    where name <> 'curly' or  number <> 1  -- 5 results
    logical, eh?

    nyuk nyuk nyuk

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2006
    Posts
    56
    Well, for
    where name <> 'curly' and number <> 1

    My logic is flawed. I would have expected 5 results because a row would have to fail both conditions to be included, not just one.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, your logic is flawed

    what you might have been thinking of was

    ... where not ( name = 'curly' and number = 1 )

    but that's clearly not the same thing as

    ... where name <> 'curly' and number <> 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2006
    Posts
    56
    It makes sense, thanks for your help

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Another way to write this (which in IMHO is clearer) is

    Code:
    select count(*) from struct where NOT (structure_name='$R' and depth='6');

    Edited: I just noticed that R937 already showed how to use NOT.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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