Solved

SQL 2005 t-sql

Posted on 2012-03-09
6
186 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 500 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 51

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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:ScottPletcher
ID: 37703406
WHERE
    column1 + column2 + column3 LIKE '%Y%Y%'
0
 
LVL 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now