Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL 2005 t-sql

Posted on 2012-03-09
6
Medium Priority
?
212 Views
Last Modified: 2012-03-11
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
0
Comment
Question by:dastaub
6 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 37702861
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
 

Expert Comment

by:TSleeman
ID: 37702957
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 37703058
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:dastaub
ID: 37703245
>>how about

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

it could be any two combination of column1, column2, or column3
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37703406
WHERE
    column1 + column2 + column3 LIKE '%Y%Y%'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37704428
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question