Say I have a biq query table with 3 columns
customer_id, name and type.
Also, say in my query, the where clause will always have customer_id for sure. But in some cases, I want to fetch all the rows for a customer_id no matter what the type but in some cases, I want to fetch only the rows where type in (<>) is satisfied.
I can write 2 separate queries where in one of them I do have both the customer_id and type in (<>) and a second query with only customer_id.
Original ask
How do I write this in one single query say if I pass '' as type then the clause for type always evaluates to true I cannot say (and type in (<>) or true) because this will always evaluate to true. I want type to be ignored if I do not wish to filter on type
Corrected ask
How do I write this in one single query that type column is ignored in where clause at times when I want to ignore it, or it must fetch rows along with the expected values I pass for type clause. For example in some instances I do not want type clause to impact the result of query and in another instance, I may have values v1,v2 for type and I want to fetch rows by filtering on those types as well.
The logic should be generic so that I can extend it to more columns
If I understand correctly , you want to only apply type filter if a valid value for type is provided. If type is an empty string , the type condition should be ignored, but the customer_id condition should still be applied.
Let me know if the my understanding is not correct.
you can try something like this :
SELECT customer_id, name, type
FROM your_table
WHERE customer_id = @customer_id
AND (@type = '' OR type IN (@type));
For example :
If you pass @type = '' (empty string): The query will return all rows for customer_id = 123, regardless of the type.
SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
AND ('' = '' OR type IN ('')); -- type filter is ignored, returns all rows for customer_id 123
If you pass @type = 'A': The query will return rows where customer_id = 123 and type is A.
SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
AND ('A' = '' OR type IN ('A')); -- only returns rows for customer_id 123 with type 'A'
If you pass @type = 'A', 'B': The query will return rows where customer_id = 123 and type is either A or B.
SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
AND ('A,B' = '' OR type IN ('A', 'B')); -- returns rows for customer_id 123 with type 'A' or 'B'