We help IT Professionals succeed at work.

SQL 2005 t-sql

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

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,
how about

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

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

Commented:
what about

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

Open in new window

Author

Commented:
>>how about

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

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

Commented:
WHERE
    column1 + column2 + column3 LIKE '%Y%Y%'
Guy Hengel [angelIII / a3]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)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.