Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check same long condition on two columns

Tags:

sql

postgresql

I have two columns X and Y and a where statement as following:

WHERE (x=0 or x=1 or x=4 or x=6 or x=7 or x=12 or x=75) and 
      (y=0 or y=1 or y=4 or y=6 or y=7 or y=12 or y=75) 

since it's the same condition on both columns of the same table is there a way to short it?

Something like x and y are (0 or 1 or 4....) - it's not PostgreSQL syntax but its clarify the question.

Note: the numbers represent statuses, there is no mathematical logic behind this condition.

like image 377
John Avatar asked Jun 18 '15 10:06

John


2 Answers

Provided that you don't need it to use an index on x or y, the simplest way is probably:

...WHERE ARRAY[x,y] <@ ARRAY[0,1,4,6,7]
like image 123
Nick Barnes Avatar answered Oct 10 '22 13:10

Nick Barnes


You may put the values into an array and the array into a CTE.

Assuming the existence of tablename(x int, y int):

with list(arr) as (select array[0,1,4,6,7])
select x,y from list,tablename where x=any(arr) and y=any(arr);
like image 42
Daniel Vérité Avatar answered Oct 10 '22 13:10

Daniel Vérité