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
dastaubAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jogosConnect With a Mentor 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
0
 
TSleemanCommented:
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.
0
 
HainKurtSr. System AnalystCommented:
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

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
dastaubAuthor Commented:
>>how about

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

it could be any two combination of column1, column2, or column3
0
 
Scott PletcherSenior DBACommented:
WHERE
    column1 + column2 + column3 LIKE '%Y%Y%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
0
All Courses

From novice to tech pro — start learning today.