"Select = any()" works but "select != any()" fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"Select = any()" works but "select != any()" fails

Author

Topic

MarkSainsbury
Starting Member

3 Posts

Posted - 2008-11-30 : 05:14:16

The following query finds duplicate cell numbers in a database based on the last 9 digits in the number. It finds 3 numbers, each with one match/duplicate (total of 6 numbers).

select '0' + right(cell_number,9) as cell_number, [name], surname
from contact
where right(cell_number,9)=any
(
select right(cell_number,9) as cell_number
from central20081127..contact
where len(cell_number)>9
and len(cell_number)<13
group by right(cell_number,9)
having count(*)>1
)

The following query however finds all the numbers in the database, including the 6 numbers I'm trying to exclude!

select '0' + right(cell_number,9) as cell_number, [name], surname
from contact
where right(cell_number,9)!=any
(
select right(cell_number,9) as cell_number
from central20081127..contact
where len(cell_number)>9
and len(cell_number)<13
group by right(cell_number,9)
having count(*)>1
)
and len(cell_number)>9
and len(cell_number)<13

The next query give me the result I actually need, but it's hopelessly inelegant. If I got 30 or 300 numbers duplicated, this would be a terrible solution:

select '0' + right(cell_number,9) as cell_number, [name], surname
from contact
where right(cell_number,9) != '83xxxxxxx'
and right(cell_number,9) != '83yyyyyyy'
and right(cell_number,9) != '83zzzzzzz'
and len(cell_number)>9
and len(cell_number)<13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 06:52:25

What you want is just this

select '0' + right(c.cell_number,9) as cell_number, c.[name], c.surname 
from contact c
left join (select right(cell_number,9) as cell_number
from central20081127..contact
where len(cell_number)>9
and len(cell_number)<13
group by right(cell_number,9)
having count(*)>1) dup
on dup.cell_number=right(c.cell_number,9)
where dup.cell_number is null

!= any() wont work for you as it returns all records whose right(cell_number,9) is not equal to any of the duplicate value pairs which is always true (thats why you get all the numbers in table)

MarkSainsbury
Starting Member

3 Posts

Posted - 2008-11-30 : 10:37:05

Thanks for the reply and assistance, but I'm still not sure why my query doesn't work.

I have an identical subquery in both the "=any" and the "!=any" examples, and the subquery finds three numbers of length 9. In the first example I say, "Find me all numbers that equal the last 9 digits of the three numbers from the subquery", and it finds me 6 numbers that match. In the second example I say, "Find me all numbers that do not equal the last 9 digits of the three numbers from the subquery" and it finds me all 3339 results where it should find me 3333 results (my third example finds me 3333 results).

Surely these statements are exclusive and where one finds me 6 results, the other should find me "total-6" results?

While I'm clearly wrong, I don't agree that the "right(cell_number,9)!=any" is always going to be true.

Please explain as you're obviously right, but I don't understand why and I need to understand to make sure I don't bang my head on the same wall in the future.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 11:47:02

quote:
Originally posted by MarkSainsbury

Thanks for the reply and assistance, but I'm still not sure why my query doesn't work.

I have an identical subquery in both the "=any" and the "!=any" examples, and the subquery finds three numbers of length 9. In the first example I say, "Find me all numbers that equal the last 9 digits of the three numbers from the subquery", and it finds me 6 numbers that match. In the second example I say, "Find me all numbers that do not equal the last 9 digits of the three numbers from the subquery" and it finds me all 3339 results where it should find me 3333 results (my third example finds me 3333 results).

Surely these statements are exclusive and where one finds me 6 results, the other should find me "total-6" results?

While I'm clearly wrong, I don't agree that the "right(cell_number,9)!=any" is always going to be true.

Please explain as you're obviously right, but I don't understand why and I need to understand to make sure I don't bang my head on the same wall in the future.

Thanks

its so obvious

just imagine this condition

right(cell_number,9)!=any(something)
for each record you take out of outer query, there will atleast a single record whose right(cell_number,9) <> right(cell_number,9) of your table. so whatever data sample you take, it will always return you full table data (in effect it works like a trivial solution 1=1 which is always true)

MarkSainsbury
Starting Member

3 Posts

Posted - 2008-11-30 : 14:19:34

Thanks! Basically I was using a term that was too broad: 'any'.

I still thought that it was possible to use the logic that I applied earlier (ie "if I get a subset of the results using an equality, I must get the remaining results if I use an inequality"). And it is. By substituting "not in" where I had "!=any" I got the result that I wanted. My subsequent solution is:

select '0' + right(cell_number,9) as cell_number, [name], surname
from contact
where right(cell_number,9) not in
(
select right(cell_number,9) as cell_number
from contact
where len(cell_number)>9
and len(cell_number)<13
group by right(cell_number,9)
having count(*)>1
)
and len(cell_number)>9
and len(cell_number)<13

Thanks for getting back to me because the "any" had meanings that I was not looking at, even though they are, as you say, very obvious! This has been instructive on a few levels!

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:57:06

No problem..you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources