SQL Where clause greater/less than multiple numeric columns

[#1]

I never thought I'd be asking an SQL question here. However today is the day!

Say I have a table with numeric columns A, B populated as follows. We can think of column A being a foreign key to a parent table, and column A and B together being the primary key of its child table:

A  B
1  1
1  2
2  1
2  2
3  1
3  2

I would like a SQL Select to return all records after, for example, (2, 1). I.e. (2, 2), (3, 1), and (3, 2).

Obviously the following won't work (it won't return (3, 1)):

select A, B from mytable when A > 2 and B > 1

I wish there were a way to write "A > 2 and B > 1" in a way that indicates B is "is a breakdown" of A, for lack of a better way to express it.

Of course I could create a derived column with the two numbers concatenated together, padded with enough zeros to accomodate maximum number size. Something like:

A  B  AandB
1  1  0101
1  2  0102
2  1  0201
2  2  0202
3  1  0301
3  2  0302

... Then I could write the SQL I want as:

select A, B from mytable when AandB > '0201'

However it would be wonderful if I could write a Where clause operating on the original numbers.

Maybe it would have been best to avoid multiple numeric columns making up a child table's key, although I'm not sure avoiding such would always eliminate the need for what I'm asking about.

This has been a tough one to Google search for solutions to. Thoughts and ideas are welcome!

[VolkerBarth]

I'd guess the folowing should do:

select A, B from mytable where A = 2 and B > 1 or A > 2;

[dhkom]

Thanks, Volker. I probably should have figured that out too - sometimes we're just blocked. Thinking more about this, multi-level numeric primary keys should probably be avoided in favor of surrogate keys. This may have eliminated my need here. However the database in question is well established as it is.

[VolkerBarth]

The somewhat shorter answer for those aware of the higher precedende of AND vs. OR...:)

[johnsmirnios]

It's fine to rely on precedence but I have had too many first hand experiences in multiple languages where the author clearly assumed the wrong precedence 🙂 There's no mistaking the order of operations when the parentheses are given.

My favourite one from C/C++ is

bool flag_is_set = flags&SOME_FLAG_MASK != 0;

It does not do what the author intended. It does the following:

bool flag_is_set = flags & (SOME_FLAG_MASK != 0);

which will always return zero if SOME_FLAG_MASK is zero or the low bit of flags otherwise.

I've also seen

if( a &&

b || c &&

d )

{ ... }

and they clearly meant (b || c)

[VolkerBarth]

Well, I do get the point. On the other hand, I certainly dislike it when each and every condition is put into parantheses, as some tools do... It just makes it harder to read, and usually there is common knowledge of precedence rules. (Bitwise operators are less common, for sure...)