We help IT Professionals succeed at work.

# SQL 2005 t-sql

on
i need a SQL WHERE clause that gives me all rows that have a Y in any 2 of 3 possible columns.
That is
column1  column2 column3
Y                  N              Y     = meets criteria
Y                  N              N  = not meets

i need all rows that 2 of the columns have a Y regardless of what 2 columns of the 3 columns
Comment
Watch Question

## View Solution Only

Commented:
WHEN (case when column1 = 'Y' then 1 else 0 end
+ case when column2 = 'Y' then 1 else 0 end
+ case when column3 = 'Y' then 1 else 0 end) >= 2

Commented:
Hi,

Select * from TableName where column2="Y" or column3 = "Y"

This works on MYSQL and should be ok on MS SQL as well.
Sr. System Analyst
BRONZE EXPERT

Commented:

len(replace(column1+column2+column3,'N',''))>=2

``````with t as (
select 1 id, 'Y' column1, 'N' column2, 'Y' column3
union select 2,'N','N','Y'
union select 3,'Y','Y','Y'
union select 4,'Y','N','N'
union select 5,'N','Y','Y'
)
select * from t where len(replace(column1+column2+column3,'N',''))>=2

id	column1	column2	column3
1	Y	N	Y
3	Y	Y	Y
5	N	Y	Y
``````

Commented:

>>Select * from TableName where column2="Y" or column3 = "Y"

it could be any two combination of column1, column2, or column3
Senior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
WHERE
column1 + column2 + column3 LIKE '%Y%Y%'
Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
plenty of good suggestions already :)

anyhow, I will suggest another one, a bit more complex.
you could create a computed column that returns the number of counts of Y in your row, and use that column as criteria.

with your simple case it won't be more efficient, but if you have to query this over and over again, you might consider to index the column (which will make it persistent), and the calculations will not be done over and over again, but just on row update (and insert)