CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 1

How to select rows that meet criteria from two different columns

I have this small table (8 rows) called songs and I need to select just the title and length of all songs that are less than 3 minutes and 46 seconds long. The longest a song can be is 3 minutes and 45 seconds.

Songs table image

I previously tried:

SELECT title, length FROM songs WHERE minutes <= 3 AND seconds < 46;

but this command left out the song titled A Kind of Magic that is 2 minutes and 59 seconds in length.

I'm thinking that this SELECT query might need a CASE expression but can't figure out how to make it work without getting an error.

Upvotes: 0

Views: 39

Answers (2)

MattTriano
MattTriano

Reputation: 1542

Your query left out that song because its "seconds" column has the value 59, which is greater than 46, making the second condition false.

The database doesn't understand the relationship between "minutes" and "seconds", so you have to explicitly define that relationship via a command like 

SELECT title, length
FROM songs
WHERE ((minutes * 60) + seconds) < 226;

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. Just convert your minutes to seconds.

SELECT title, length FROM songs WHERE ((minutes * 60) + seconds) <= (3 * 60) + 46;

or

SELECT title, length FROM songs WHERE ((minutes * 60) + seconds) <= 240; --in seconds

Upvotes: 1

Related Questions