?
Solved

SQL 2005 t-sql

Posted on 2012-03-09
6
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 58

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 69

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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