Whats wrong with this query?

Question

Whats wrong with this query?

cstrati

SSChasing Mays

Points: 606
More actions
July 10, 2014 at 5:28 am

#296430

select mvID, title, rating, length ,studio
from MovieInfo
where rating not like 'R'
and
where (length > 90)
Seems to be a problem with line 5
Thanks in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

cstrati (7/10/2014)
select mvID, title, rating, length ,studio
from MovieInfo
where rating not like 'R'
and
where (length > 90)
Seems to be a problem with line 5
Thanks in Advance.

Query should be

select mvID, title, rating, length ,studio

from MovieInfo

where rating not like 'R'

AND (length > 90)

Thanks

Neil Burton SSC-Insane Points: 23002 More actions · Answer 2

Edit because I missed the obvious problem.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Hi, just remove the second 'where'.

Only 1 where per select in a query - combine conditions using and/or

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

cstrati (7/10/2014)
select mvID, title, rating, length ,studio
from MovieInfo
where rating not like 'R'
and
where (length > 90)
Seems to be a problem with line 5
Thanks in Advance.

There may be a problem with line 2 also. Do you mean

WHERE rating NOT LIKE 'R%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Without the wild card on the comparison to 'R' you might as well drop the LIKE statement and say <> instead.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning