CODEWITHSUNDEEP
mysqlbetween
Mike
Mike

Reputation: 21

Between but NOT inclusive with sub query

I have seen replies to this when using dates but not with sub queries. I have the following

SELECT  *
FROM  `TEST` 
where `ID` BETWEEN
(SELECT `ID`  FROM `TEST` WHERE `Home_Team`
REGEXP 'saturday|sunday|monday|tuesday|wednesday|thursday|friday' 
order by ID asc LIMIT 1)
AND 
(SELECT `ID` FROM `TEST` WHERE `Home_Team`
REGEXP 'saturday|sunday|monday|tuesday|wednesday|thursday|friday' 
order by ID asc LIMIT 1,1)

I would like the results not to be inclusive. unfortunately, im not having any luck with any of < > =

Upvotes: 0

Views: 882

Answers (2)

Michael
Michael

Reputation: 12806

Is ID an integer? If so, just +1 and -1 where appropriate:

SELECT  *
FROM  `TEST` 
where `ID` BETWEEN
(SELECT `ID`  FROM `TEST` WHERE `Home_Team`
REGEXP 'saturday|sunday|monday|tuesday|wednesday|thursday|friday' 
order by ID asc LIMIT 1) + 1
AND 
(SELECT `ID` FROM `TEST` WHERE `Home_Team`
REGEXP 'saturday|sunday|monday|tuesday|wednesday|thursday|friday' 
order by ID asc LIMIT 1,1) - 1

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

Best I'm aware, a BETWEEN b and c is syntactic sugar for b <= a and a <= c, i.e. always inclusive. To make it exclusive, rewrite it as b < a and a < c.

Upvotes: 2

Related Questions