How to apply multiple between operator in SQL

Question

I have a journal type table where it has Account, Credit_Debit and Amount columns. I am trying a select where by the result should look like a row followed by its contra amount row. Amount column is Numeric/Decimal.

Expected output: An amount followed by its equivalent opposite(plus or minus) amount

expected output

|Account|c_d|Amount|
| 1     |D  | 100  |
! 7     |C  | -100 |
| 8     |D  | 750  |
| 1     |C  | -750 |
| 10    |C  | -500 |
| 11    |D  | 500  |

So, my query below threw invalid relational operator

select * from table where amount between 100 and 1000 AND (-100) - (-1000)

This one returned empty result set

select * from table where 
 (amount between('100') AND ('1000')) AND (amount between('-100') AND ('-1000'))

Is the presented sample of data expected output(looks like it) or the source data? If it's expected result, it'll be a good idea to post a sample of source data. — Nick Krasnov
– Nick Krasnov, Commented Sep 22, 2014 at 11:55
I think you have to change the AND in middle of the amount statements to OR. — GuyT
– GuyT, Commented Sep 22, 2014 at 11:56
between('100') AND ('1000')) is really bad coding style. First numbers should not be enclosed in single quotes. '100' is a character literal (aka "string"), not a number. 100 is a number literal. Secondly: the parentheses around the numbers ('-100') useless. — user330315
– user330315, Commented Sep 22, 2014 at 11:58
Is the AMOUNT column a character string, or is it a NUMBER? In the last comparison you've made the literal values all character string by putting them inside apostrophes. If AMOUNT is a character string you're probably going to have real problems getting your comparisons to work. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Sep 22, 2014 at 11:59

Bala · Accepted Answer · 2014-09-22 12:06:21Z

1

Ok, this has worked. Thanks for everyone's efforts.

select * from table where amount between 100 AND 1000
UNION
select * from table where amount between -1000 AND -100

answered Sep 22, 2014 at 12:06

Bala

11.3k19 gold badges75 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hrabal · Accepted Answer · 2014-09-22 12:17:53Z

1

Your second one will work with an OR operator.

select * from table where (amount between('100') AND ('1000')) OR (amount between('-100') AND ('-1000'))

But it will show rows containing amounts between -1000 and 1000 minus those with amounts between -100 and 100.

To do what you want to do, if I get it right, you can sue:

SELECT T1.amount, T2.amount from table T1, table T2 WHERE ABS(T1.amount) = ABS(T2.amount)

edited Sep 22, 2014 at 12:17

answered Sep 22, 2014 at 11:55

Hrabal

2,5332 gold badges24 silver badges31 bronze badges

Comments

Vivek S. · Accepted Answer · 2014-09-22 11:59:07Z

0

Try this:

select * from table where 
 (amount between('100') AND ('1000')) AND (amount between('-1000') AND ('-100'))

edited Sep 22, 2014 at 11:59

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

answered Sep 22, 2014 at 11:54

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

2 Comments

Bala Over a year ago

Please could you read my question and the things I have tried

MontyPython Over a year ago

Use OR not AND while joining the two where clauses.

Collectives™ on Stack Overflow

How to apply multiple between operator in SQL

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related