Thread: Between with a list of ranges possible ?

Hi,

Can I do the below 3 queries in a single query  ?

select * from table where number * 3  between start_value1 and end_value2;
select * from table where number * 3  between start_value2 and end_value2;
select * from table where number * 3  between start_value3 and end_value3;


--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as
possible,you are, by definition, not smart enough to debug it. 

--Brian Kernighan

On 5/29/2015 9:32 AM, Arup Rakshit wrote:
> Can I do the below 3 queries in a single query  ?
>
> select * from table where number * 3  between start_value1 and end_value2;
> select * from table where number * 3  between start_value2 and end_value2;
> select * from table where number * 3  between start_value3 and end_value3;

select * from table where (number * 3  between start_value1 and end_value2) OR
            (number * 3  between start_value2 and end_value2) OR
            (number * 3  between start_value3 and end_value3);





--
john r pierce, recycling bits in santa cruz

Arup Rakshit <aruprakshit@rocketmail.com> wrote:

> Hi,
>
> Can I do the below 3 queries in a single query  ?

OR ?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 29/05/2015 17:32, Arup Rakshit wrote:
> Hi,
>
> Can I do the below 3 queries in a single query  ?
>
> select * from table where number * 3  between start_value1 and end_value2;
> select * from table where number * 3  between start_value2 and end_value2;
> select * from table where number * 3  between start_value3 and end_value3;

If they're all the same table, just use OR:

... where (number * 3 between start_value1 and end_value1) or (number *
3 between....) etc.

Or am I missing something?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 5/29/2015 10:41 AM, John R Pierce wrote:
> On 5/29/2015 9:32 AM, Arup Rakshit wrote:
>> Can I do the below 3 queries in a single query  ?
>>
>> select * from table where number * 3  between start_value1 and
>> end_value2;
>> select * from table where number * 3  between start_value2 and
>> end_value2;
>> select * from table where number * 3  between start_value3 and
>> end_value3;
>
> select * from table where (number * 3  between start_value1 and
> end_value2) OR
>             (number * 3  between start_value2 and end_value2) OR
>             (number * 3  between start_value3 and end_value3);

oh, do note, this won't be /exactly/ the same if the ranges overlap.
your first would return the overlapping rows for each query, while the
OR version will just return one of each row that is in any of the ranges.

--
john r pierce, recycling bits in santa cruz

Use UNION ALL:

select * from table where number * 3  between start_value1 and end_value2
UNION ALL
select * from table where number * 3  between start_value2 and end_value2
UNION ALL
select * from table where number * 3  between start_value3 and end_value3;


-- Stephen

On 5/29/2015 12:32 PM, Arup Rakshit wrote:
> Hi,
>
> Can I do the below 3 queries in a single query  ?
>
> select * from table where number * 3  between start_value1 and end_value2;
> select * from table where number * 3  between start_value2 and end_value2;
> select * from table where number * 3  between start_value3 and end_value3;
>
>