postgres sql select combination of two columns

Question

A user can choose a list of option combinations and then search for them.

The sql could look like this.

select * from p where (option_type = 'X' and value = 'A') 
or (option_type = 'X' and value = 'B')
or (option_type = 'Y' and value = 'D')

But of course I do not want to have n number of or's

How would a good sql look like that performs ??? The user can choose many option combinations.

Thanks.

that might be the best way... and the logic is clear for readability. you can make a table (maybe temp) to store those values and join on it... why is an OR for each possible condition unacceptable? — gloomy.penguin
– gloomy.penguin, Commented Sep 16, 2013 at 17:17
you could try..... WHERE (option_type = 'X' and value in ('A','B')) or (option_type = 'Y' and value in ('D', ... ) — gloomy.penguin
– gloomy.penguin, Commented Sep 16, 2013 at 17:25
But that would also give me many or's. The user could possible choose hundreds of options — klind
– klind, Commented Sep 16, 2013 at 17:27
Are these pairs stored somewhere? Is their a rule for generating them? — PM 77-1
– PM 77-1, Commented Sep 16, 2013 at 17:28
what's the limit for the number of ors that is acceptable? i'm not sure there's another way besides storing the pair combinations in a table and joining on those values. maybe... if you provide us with sample data, table structure, where these combinations come from and example input there's a better approach someone can suggest — gloomy.penguin
– gloomy.penguin, Commented Sep 16, 2013 at 17:31

user330315 · Accepted Answer · 2013-09-16 20:44:16Z

9

No need for multiple ORs:

select * 
from p 
where (option_type, value) in ( ('X' ,'A'), ('X','B'), ('Y','D') )

answered Sep 16, 2013 at 20:44

user330315

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

2 Comments

klind Over a year ago

Oh I need a little more explanation. So I have a product that has some options. One product could have option X,A and X,B and Y,D. select * from product join product_option on product.id = product_option.product_id where (option_type, value) in ( ('X' ,'A'), ('X','B'), ('Y', 'D') ) so would not give me the right result...

user330315 Over a year ago

@user2073955: my query is equivalent to the one in your question. It solves the "*I do not want to have n number of or's" question. If you need something different you should add some sample data and the expected output.

Collectives™ on Stack Overflow

postgres sql select combination of two columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related