asked on 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
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
Microsoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
Guy Hengel [angelIII / a3]
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
what about
len(replace(column1+column
len(replace(column1+column
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
ASKER
>>how about
>>Select * from TableName where column2="Y" or column3 = "Y"
it could be any two combination of column1, column2, or column3
>>Select * from TableName where column2="Y" or column3 = "Y"
it could be any two combination of column1, column2, or column3
WHERE
column1 + column2 + column3 LIKE '%Y%Y%'
column1 + column2 + column3 LIKE '%Y%Y%'
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)
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)
how about
Select * from TableName where column2="Y" or column3 = "Y"
This works on MYSQL and should be ok on MS SQL as well.