Query help – WHERE (multiple columns) IN (multiple terms)

I am trying to write a query that I can easily reuse where I search for multiple terms within multiple columns.

So here is a query that works perfectly:

SELECT * FROM table WHERE '123' IN (col1, col2, col3, col4, col5) OR '456' IN (col1, col2, col3, col4, col5) 

My issue with this is that I want to construct my query in python using variables. Right now it’s only 123 and 456 I’m looking for, but for another query it could be just those two numbers or it could be 2112, 2113, 2114, 2115, and 2116 and it could be col1-20 or just col1-5. So I want something like:

stuff = ['123', '456'] cols = ['col1', 'col2', 'col3', 'col4', 'col5'] table_name = table query = """ SELECT * FROM """ + table_name + """ WHERE (""" + ( || ).join(cols) + ") IN (" + (, ).join(stuff) + ")" 

Which results in a query that looks like:

SELECT * FROM table WHERE (col1 || col2 || col3 || col4 || col5) IN ('123', '456') 

This query doesn’t return all the results I expect, only a small fraction. Is there a way to put this into one line so I can use variables?

submitted by /u/chilivanilli
[link] [comments]


Go to Source of this post
Author Of this post: /u/chilivanilli
Title Of post: Query help – WHERE (multiple columns) IN (multiple terms)
Author Link: {authorlink}