CODEWITHSUNDEEP
mysqlsql
user2537388
user2537388

Reputation: 50

SQL code need help understanding

I'm working on a movie database and I thought it might be a good idea to have some type of parental control in place for the future. I wrote some SQL code and it works for the most part, but i don't know why.

In my main movies table I have movies rated with the standard rating g, pg, pg-13, r, and NC-17.

Here is the SQL code i used 

Select title
From movies
Where rating < "r";

I works though it still shows the NC-17 shows. If I change the r rating to NC-17 it only shows the g rated shows.

I know I can type out a longer SQL to give me the matches I want, but I want to understand why this code is performing the way it is.

Thanks for the help.

Upvotes: 0

Views: 755

Answers (3)

Linger
Linger

Reputation: 15048

Here is the query that would probably work for you if you want to rank them:

SELECT m.title, m.rating
FROM 
(
  SELECT s.title, s.rating, 
         CASE WHEN s.rating = 'G' THEN 1 
              WHEN s.rating = 'PG' THEN 2
              WHEN s.rating = 'PG-13' THEN 3
              WHEN s.rating = 'R' THEN 4
              WHEN s.rating = 'NC-17' THEN 5
              ELSE 6 END AS MovieRanking
  FROM movies s
) m
WHERE m.MovieRanking < 4

Upvotes: 0

Coda17
Coda17

Reputation: 599

SQL doesn't understand the movie rating system. The < operator looks at strings in alphabetical order. So when you say < 'R', it's looking for for all ratings that start with a letter before R in the alphabet. Since there are a limited number of options for ratings, you're best off doing something along the lines of this:

SELECT title
FROM movies
WHERE rating NOT LIKE 'R'
AND   rating NOT LIKE 'NC-17'

Upvotes: 0

John Conde
John Conde

Reputation: 219804

How is MySQL to know R is less than NC-17? MySQL knows how to sort numbers and letters but not movie ratings. You have to assign the ratings numbers and sort based on that.

For example:

Rating    Value
------------------
  G         1
  PG        10
  PG-13     20
  R         30
  NC-17     40

Than give each movie the numerical value of the rating (or use a join) and then sort on that.

Upvotes: 4

Related Questions