SQL IN Operator with different comparators

Question

I need a way to make a IN operator that makes different kind of comparisons for each parameter, that way:

SELECT * 
FROM Table 
WHERE (par1, par2, par3) IN ((par1answer1, par2answer1, par3min1, par3max1),
                             (par1answer2, par2answer2, par3min2, par3max2),
                             ...,
                             (par1answern, par2answern, par3minn, par3maxn)

)

Explanation:

par1 needs to be EQUAL to par1answer1 or par1answer2
par2 needs to be EQUAL to par2answer1 or par2answer2
par3 needs to be BETWEEN par3min1 and par3max1, or par3min2, par3max2

@EDIT
Sorry for not being clear, I'm not really good at explanations, but I'm working on it!
Example set:

par1    par2    par3
2       5       10
3       6       20
4       7       30
5       8       60

SELECT * 
FROM Table 
WHERE (par1, par2, par3) IN ((2,5,9,11),
                             (3,6,11,19),
                             (5,7,9,100),
                             (5,8,10,80),
                             (2,8,0,200))

Result:
par1    par2    par3
2       5       10    //matches first tuple
5       8       60    //matches fourth tuple

Not really sure what you are asking, but the answer will involve one or more joins. — joepd, Nov 20 '15 at 19:20

score 4 · Accepted Answer · answered Nov 20 '15 at 19:32

4

I am guessing you really want this:

WHERE ( (par1, par2) = (par1answer1, par2answer1) and
        par3 between par3min1 and par3max1
      ) or
      ( (par1, par2) = (par1answer2, par2answer2) and
        par3 between par3min2 and par3max2
      )

This is not exactly how your explanation goes, but it does seem like a reasonable interpretation of your attempted query.

answered Nov 20 '15 at 19:32

Gordon Linoff

1,242,037
58
646
786

yeah based on the comment on @a_horse_with_no_name 's answer this is what he wants. – Hogan Nov 20 '15 at 19:33
Beside oracle what other db support `tuple` comparasion? – Juan Carlos Oropeza Nov 20 '15 at 19:33
@JuanCarlosOropeza - db2 does. It was part of a standard from way back in... hmmm... I'm going to say '92 – Hogan Nov 20 '15 at 19:33
What about mySql, Postgres and MsSQL ? – Juan Carlos Oropeza Nov 20 '15 at 19:34
1

@JuanCarlosOropeza: Postgres, DB2, HSQL, MySQL, H2 - this is part of the SQL standard – Nov 20 '15 at 19:34
Gordon, I have no idea how you could mind-read that from the question ;) – Nov 20 '15 at 19:35
Sorry for not being clear. This is kinda what I want, but with n tuples inside the IN. Anyway, I could make this query work by making a for loop within my java code and adding more ORs as the number of parameters grow. This shouldn't be a performance issue, since the number of tuples will never go up to 10 anyway. Upvoted and Marked – Lucas Nov 20 '15 at 19:53

score 2 · Answer 2 · answered Nov 20 '15 at 19:28

2

Just write out the comparisons by hand. IN won't do what you want.

where (par1 = par1answer or par1 = par1answer2)
and (par2 = par2answer1 or par2 = par2answer2)
and (par3 between par3min1 and par3max1 or par3 between par3min2 and par3max2)

answered Nov 20 '15 at 19:28

Becuzz

6,846
26
39

SQL IN Operator with different comparators

2 Answers2