mysql select where greater than less than multiple columns

Question

I have a column that has month and years as columns.. I need to select a range out of the columns.. they don't seem to work with 2 ranges.

Table

CREATE TABLE `sampletable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `AccountId` int(11) unsigned NOT NULL,
  `CampaignId` int(11) unsigned NOT NULL,
  `CampaignName` varchar(115) NOT NULL DEFAULT '',
  `Sent` int(11) unsigned NOT NULL,
  `Month` int(11) unsigned NOT NULL,
  `Year` int(11) unsigned NOT NULL,
  `LocationId` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AccountId_idx` (`AccountId`),
  KEY `monthy_year_idx` (`Month`,`Year`),
  KEY `locationid_idx` (`LocationId`)
) ENGINE=MyISAM AUTO_INCREMENT=1584

Select statement:

SELECT * FROM sampletable
WHERE AccountId = 1 
and (`Month` >= 10 AND `Year` = 2012)
and (`Month` <= 2 AND `Year` = 2013)
ORDER BY Year asc, month asc

This does not seem to work.

Do I have to convert these into date formats and use between?

JHS · Accepted Answer · 2013-01-24 14:25:40Z

6

What you are doing is something similar to "trying to go left and right at the same time".

Lets break the WHERE clause.

You are telling MySQL to get rows with

Year = 2012 AND Year = 2013
Month >= 10 AND Month <= 2

This will never be true.

Your WHERE clause should look like -

WHERE AccountId = 1 
and ((`Month` >= 10 AND `Year` = 2012)
OR (`Month` <= 2 AND `Year` = 2013))

answered Jan 24, 2013 at 14:25

JHS

7,8812 gold badges34 silver badges54 bronze badges

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

Comments

Edward Wong · Accepted Answer · 2013-01-24 14:24:33Z

1

 and (     
  (`Month` >= 10 AND `Year` = 2012)
 or (`Month` <= 2 AND `Year` = 2013))

answered Jan 24, 2013 at 14:24

Edward Wong

463 bronze badges

Comments

John Woo · Accepted Answer · 2013-01-24 14:25:15Z

1

enclose the condition around parenthesis and use OR

SELECT ...
FROM ...
WHERE AccountId = 1  AND   
      ((`Month` >= 10 AND `Year` = 2012) OR  (`Month` <= 2 AND `Year` = 2013) )

answered Jan 24, 2013 at 14:25

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Hamlet Hakobyan · Accepted Answer · 2013-01-24 14:23:36Z

0

To achieve what you want use 'or' instead of 'and'.

answered Jan 24, 2013 at 14:23

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

mysql select where greater than less than multiple columns

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related