MySQL incorrect output not sure whats wrong with the query

Question

Trying to write and execute a MySQL query to display the movie number, movie title, movie cost, and movie genre for all movies that are either action or comedy movies and that have a cost that is less than $50. Sort the results in ascending order by genre.

mysql> select mv_no, mv_name, mv_cost, mv_genre from movie    
    -> where mv_genre = ('Action' or 'Comedy') and mv_cost < 50    
    -> order by mv_genre;

+-------+----------------------------+---------+----------+
| mv_no | mv_name                    | mv_cost | mv_genre |    
+-------+----------------------------+---------+----------+    
| 1245  | Time to Burn               | 45.49   | ACTION   |    
| 1237  | Beatnik Fever              | 29.95   | COMEDY   |    
| 1239  | Where Hope Dies            | 25.49   | DRAMA    |    
| 1234  | The Cesar Family Christmas | 39.95   | FAMILY   |    
+-------+----------------------------+---------+----------+
4 rows in set, 10 warnings (0.00 sec)

its not suppose to show drama or family movies

Daniel
– Daniel

2013-04-08 15:57:49 +00:00
Commented Apr 8, 2013 at 15:57 — Daniel
– Daniel, Commented Apr 8, 2013 at 15:57

Nir Alfasi · Accepted Answer · 2013-04-08 15:55:25Z

1

Change to:

select mv_no, mv_name, mv_cost, mv_genre from movie

where (mv_genre = 'Action' or mv_genre = 'Comedy') and mv_cost < 50

order by mv_genre;

answered Apr 8, 2013 at 15:55

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taryn · Accepted Answer · 2013-04-08 15:55:08Z

0

Had you tried using an IN clause:

select mv_no, mv_name, mv_cost, mv_genre 
from movie
where mv_genre in ('Action', 'Comedy') 
  and mv_cost < 50

See SQL Fiddle with Demo

Or you can alter the way that you are using the OR clause:

select mv_no, mv_name, mv_cost, mv_genre 
from movie
where (mv_genre = 'Action' or mv_genre= 'Comedy') 
  and mv_cost < 50

answered Apr 8, 2013 at 15:55

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

Stephen Fischer Over a year ago

That's what I was going to recommend. The use of OR in the original is wrong.

Brandon Buck · Accepted Answer · 2013-04-08 15:55:34Z

0

The error appears to be in your wear clause, you could use an IN like:

WHERE mv_genre IN ('Action', 'Comedy')

Your current clause of mv_genre = ('Action' or 'Comedy') does not compute to if the genre is 'Action' or the genre is 'Comedy' it actually just means that you want to evaluate if 'Action' OR 'Comedy' is true and then compare the result to mv_genre which would never return what you expected it to. You can compare with multiple values easily using IN and then a list or you could be more verbose and compare like:

WHERE (mv_genre = 'Action' OR mv_genre = 'Comedy')

Whichever you prefer and fits your needs.

answered Apr 8, 2013 at 15:55

Brandon Buck

7,1902 gold badges33 silver badges51 bronze badges

Comments

Robbert · Accepted Answer · 2013-04-08 15:56:08Z

0

Your query should be

mysql> select mv_no, mv_name, mv_cost, mv_genre from movie
   -> where mv_genre IN ('Action', 'Comedy') and mv_cost < 50
    -> order by mv_genre;

answered Apr 8, 2013 at 15:56

Robbert

6,6226 gold badges38 silver badges64 bronze badges

Collectives™ on Stack Overflow

MySQL incorrect output not sure whats wrong with the query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related