CODEWITHSUNDEEP
mysqlsql
mllamazares
mllamazares

Reputation: 8206

Could you simplify this query?

I have a table with this structure:

CREATE  TABLE IF NOT EXISTS `mydb`.`sizes` (
  `id_size` INT NOT NULL ,
  `cm_min` INT NOT NULL ,
  `cm_max` INT NOT NULL ,
  PRIMARY KEY (`id_size`) )
ENGINE = InnoDB;

This query returns the width and the height of the size 5:

select cm_min, cm_max from sizes where id_size = 5;

which are:

45, 56

Well, I want to get all the sizes that have the cm_min or cm_max between this range. I tought something like this:

http://sqlfiddle.com/#!2/83e93/51

It works, but there is sizes that are not between any range. I mean, if i have this:

id_size    cm_min    cm_max
1          56        59
2          63        67
3          70        74
4          76        79
5          83        86
6          60        62
7          12        14

If I've the values of the id_size 6, and I execute the previous query it'll returns 0 rows selected, and I want get the id_size of the sizes higher and smaller than this, although not strictly between the values. In this case would return:

id_size    cm_min    cm_max
1          56        59
2          63        67

Or for example if have the values of te id_size 7 which is the minor value, i need get the next minor value, in my case is:

id_size    cm_min    cm_max
1          56        59

Or if i've the higher values I'd need the id of the size wich has the second higher values.

Then if the first query that i posted doesn't has any result I do this: http://sqlfiddle.com/#!2/83e93/32/0

It works to. So, my question is: Can I do the same with only one query?

I hope I was clear in my explanation and I would appreciate any help. Thanks in advance.

Upvotes: 0

Views: 86

Answers (1)

Minesh
Minesh

Reputation: 2302

Below SQL would help you get started, which will return all the rows which are higher or lower than selected id e.g. here for id = 6

SELECT id_size 
FROM sizes 
WHERE (cm_min < 60 or  cm_min > 62) and
(cm_max < 60 or cm_max > 62);

SqlFiddle Demo

Update - Example

Below I have provided more closer example to your need: Remove LIMIT 1 if you need all the records, lower and higher

SELECT id_size , 'lower'
FROM sizes 
WHERE (cm_min < 12 AND cm_max < 12)  
LIMIT 1
UNION
SELECT id_size , 'HIGHER'
FROM sizes 
WHERE (cm_min > 14 AND cm_max > 14)
LIMIT 1

SqlFiddle Demo

Upvotes: 1

Related Questions