CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 103

MySQL incorrect output not sure whats wrong with the query

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)

Upvotes: 1

Views: 420

Answers (4)

Robbert
Robbert

Reputation: 6582

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;

Upvotes: 0

Brandon Buck
Brandon Buck

Reputation: 7181

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.

Upvotes: 0

Nir Alfasi
Nir Alfasi

Reputation: 53525

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;

Upvotes: 1

Taryn
Taryn

Reputation: 247690

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

Upvotes: 0

Related Questions