NOT LIKE statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

NOT LIKE statement

Author

Topic

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-25 : 07:23:14

Hi everyone, I'm having a problem with a NOT LIKE statement:


SELECT A.ITEM_CD, B.ITEM_CD FROM

(SELECT ITEM FROM DATABASE
WHERE ITEM_CD LIKE 'A55%'
)

INNER JOIN

(SELECT ITEM_CD FROM DATABASE
WHERE ITEM_CD NOT LIKE 'A57%'

So think in terms of finding a customer who has bought one thing from a shop (A55..), but has not bought a certain other item (A57..). When I run the above query I am finding customers that have bought both A55 and A57. Why is this?

On a side note, when I search for NOT LIKE 'A57%' on it's own without joining it to the sub query for A55, the query works as expected and displays customers who have not bought A55.

Thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-25 : 07:29:50

I think you may need something more like this?

SELECT customer,ITEM
FROM [DATABASE] d
WHERE ITEM_CD LIKE 'A55%'
and not exists
(select * from [database] e where d.customer = e.customer and e.item_cd like 'A57%')

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:01:18

[code]SELECT customer
FROM [DATABASE] d
GROUP BY customer
HAVING SUM(CASE WHEN ITEM_CD LIKE 'A55%' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN ITEM_CD LIKE 'A57%' THEN 1 ELSE 0 END) = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-25 : 11:20:31

I used a left join to store the null values in a temp table. The CASE answer verified these results. The NOT EXIST answer didn't throw any exceptions, but the query never finished as I'm using a database that hold over 3 million records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources