CODEWITHSUNDEEP
mysql
fastyzfr1
fastyzfr1

Reputation: 47

mysql select where greater than less than multiple columns

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?

Upvotes: 2

Views: 7798

Answers (4)

JHS
JHS

Reputation: 7871

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

  1. Year = 2012 AND Year = 2013

  2. 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))

Upvotes: 6

John Woo
John Woo

Reputation: 263733

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) )

Upvotes: 1

Edward Wong
Edward Wong

Reputation: 46

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

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 0

Related Questions