and or problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

and or problem

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-27 : 07:08:42

so i have a table with id, pagename (which includes a category) and some other columns

the following statement doesn't seem to work and i can't figure out why

SELECT id, pagename, otherstuff
FROM tblPages
WHERE (id LIKE '1') AND (pageName NOT LIKE 'Cat1%') OR
(id LIKE '1') AND (pageName NOT LIKE 'Cat2%')

Where Cat1 and Cat2 are category names, so if i have 3 entries all with id = 1 (i know it's a crap column name, too long to explain why it's called id). I'm going through every row and trying to exclude everything with a prefix of Cat1 or Cat2, id by id. it works if i take everything after the OR out so what's going wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 07:12:43

[code]SELECT id,
pagename,
otherstuff
FROM tblPages
WHERE id LIKE '1'
AND pageName NOT LIKE 'Cat[12]%')[/code]

E 12°55'05.63"
N 56°04'39.26"

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-27 : 07:14:31

sorry, i changed the names of my prefixes for this post, they're not cat1 and cat2, they're 2 totally different words

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-27 : 08:57:02

quote:
Originally posted by neil_akoga

sorry, i changed the names of my prefixes for this post, they're not cat1 and cat2, they're 2 totally different words

Peso's code should work

Your code will work with these changes

SELECT id, pagename, otherstuff
FROM tblPages
WHERE ((id LIKE '1') AND (pageName NOT LIKE 'Cat1%')) OR
((id LIKE '1') AND (pageName NOT LIKE 'Cat2%'))

Madhivanan

Failing to plan is Planning to fail

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-27 : 09:12:44

it might work if my categories were called cat1 and cat2 but these aren't my actual column names as i don't want to post them here for obvious reasons. i think renaming them to cat1 and cat2 for the purposes of my post confused the issue. , here is what i have

SELECT id, pageName
FROM tblPages
WHERE (id LIKE '1') AND (pageName NOT LIKE 'Dogs%') OR
(id LIKE '1') AND (pageName NOT LIKE 'Cats%')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-27 : 09:21:09

quote:
Originally posted by neil_akoga

it might work if my categories were called cat1 and cat2 but these aren't my actual column names as i don't want to post them here for obvious reasons. i think renaming them to cat1 and cat2 for the purposes of my post confused the issue. , here is what i have

SELECT id, pageName
FROM tblPages
WHERE (id LIKE '1') AND (pageName NOT LIKE 'Dogs%') OR
(id LIKE '1') AND (pageName NOT LIKE 'Cats%')

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-27 : 09:28:14

sure here's an example table

id, pageName
1, Cats - How to grow your own
1, Dogs - Why do they bark
1, Fish - What to feed them
2, Cats - Why do they purr
2, Fish - Fins and gills
etc

so i want to select everything from this table that doesn't have a prefix of cats or dogs on the pagename column. I'm using sql server 2008 and visual studio 2008 to write my query. I keep trying to fit my parentheses around each "(id like '1') and (pagename not like 'whatever%')" condition but it just removes the brackets every time. does this sound like a bug in visual studio's intellisense to you? It's basically not letting me do the following

select * from table where
(id like '1' and pagename like 'cats%') or (id like '1' and pagename like 'dogs%')

it's also not letting me do the following parentheses

select * from table where
((id like '1') and (pagename like 'cats%')) or ((id like '1') and (pagename like 'dogs%'))

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-27 : 09:31:09

forgot to say that i'm looping through this table doing a select for each available id so i might do x amount of selects with x amount of id's appearing in each select - e.g. id=1 might have rows, id=2 might only have 1 row. that's the reason I have to include the ID in each select i do.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-27 : 12:25:35

If you are trying to exclude where PageName is not like "something" you should be using all ANDs and no ORs..(logic issue)

DECLARE @Table TABLE (id VARCHAR(10), pageName VARCHAR(50))

INSERT @Table
SELECT '1', 'Cats - How to grow your own'
UNION ALL SELECT '1', 'Dogs - Why do they bark'
UNION ALL SELECT '1', 'Fish - What to feed them'
UNION ALL SELECT '2', 'Cats - Why do they purr'
UNION ALL SELECT '2', 'Fish - Fins and gills'

select * from @table where
id like '1'  
and pagename not like 'cats%' 
and pagename not like 'dogs%'

-- Results
id         pageName
---------- --------------------------------------------------
1          Fish - What to feed them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources