1. Registered User
Join Date
Aug 2006
Posts
56

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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>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

3. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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.

5. SQL Consultant
Join Date
Apr 2002
Location
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

6. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

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

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

9. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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.

#### Posting Permissions

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