There is a table

CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `sq_min` int(11) NOT NULL, `sq_max` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Дамп данных таблицы `test` -- INSERT INTO `test` (`id`, `sq_min`, `sq_max`) VALUES (1, 50, 200), (2, 45, 49), (3, 210, 250), (4, 45, 55), (5, 195, 205);

How to do a search on this table. If the search is also given a minimum and maximum.

  • What do you need to find? All intervals that fall into a given? Or all intervals that contain a given? Or? .. - yozh
  • it turns out the line is a lot. you need to find all the lots, for example, which are in the interval in the lot, for example, sq_min = 50 and sq_max = 200 in the search form entered from 10 to 49 from 100 to 150 from 10 to 60 from 150 to 250 how to make a request so that it finds all except the first condition This lot - coderxlsn
  • For each interval, one query, or in one query, select records that are suitable for at least one interval from the form? Specify your question, but it is not clear. - avp
  • Yes, for each interval its request. Search from 10 to 49 or search from 100 to 150, etc. - coderxlsn

2 answers 2

 SELECT * FROM `table` WHERE (sq_min > $sq_min AND sq_min < $sq_max) AND (sq_max < $sq_max AND sq_max > $sq_min)

or

 SELECT * FROM `table` WHERE (sq_min > $sq_min AND sq_min < $sq_max) OR (sq_max < $sq_max AND sq_max > $sq_min)

Do not disassemble your text. Put punctuation marks, at least. Judging by your example, the second option is appropriate.

 SELECT * FROM `table` WHERE sq_min > $sq_min AND sq_max < $sq_max AND type=$type

$ sq_min, $ sq_max - passed from form.

  • super. Works as I wanted! - coderxlsn
  • 2
    @ Yoharny Babai And not simpler ... WHERE (sq_min < $sq_max) AND (sq_max > $sq_min) ? If we assume that both pairs are increasing and we are looking for !! (intersection). PS: Under these conditions, yours are shrinking, ok, then I suggested optimization) - Sh4dow
  • And that is true))))) - Yoharny Babai
  • and what if there are more conditions in the request. for example type = 1. I understood correctly: WHERE ((sq_min> $ sq_min AND sq_min <$ sq_max) OR (sq_max <$ sq_max AND sq_max> $ sq_min)) and type = 1 - coderxlsn
  • in the comments cuts the line break. WHERE (sq_min <$ sq_max) AND (sq_max> $ sq_min) - the option does not work. if you look for less than the lower limit .. for example, the lot has a bottom chapel of 100, and we are sampling from 90. I do not understand how to add to the query (sq_min> $ sq_min AND sq_min <$ sq_max) OR (sq_max <$ sq_max AND sq_max> $ sq_min) more 10 parameters? - coderxlsn

10 to 49 from 100 to 150 from 10 to 60 from 150 to 250 if, for each interval, one query results in 4 SQL selections per cycle. If the RAM allows me to think it is better to make one request from the very smallest 10 to the most 250 Then programmatically using Linq or sort in the old manner.