How to get distinct list when all records are true

I have a table as below
Select Id, Feld1, Feld2, Feld3 from table1

How can I get distinct list of Ids when either ( All Feld1 = 'Y'  or All Feld2 = 'Y'   or All Feld2 = 'Y'  )

Id, Feld1, Feld2, Feld3
1    Y, N, N
1    N, N, N
2    Y, N, Y
2    Y, N, N
3    N, Y, N
3    N, Y, N
3    N, Y, N

Should return  2, 3

Id, Feld1, Feld2, Feld3
1    Y, N, N
2    Y, N, Y
2    Y, N, N
3    N, Y, N
3    N, Y, N
3    N, Y, N
Should return 1, 2, 3
Bharat GuruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Does this not work? 
SELECT DISTINCT Id, Field1, Field2, Field3
FROM YourTable
WHERE Field1 = 'Y' OR Field2='Y' OR FIeld3='Y'

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
or would it be something like this:
DECLARE @data TABLE (
	id INT,
	field1 CHAR(1),
	field2 CHAR(1),
	field3 CHAR(1)
)

INSERT INTO @data ( id, field1, field2, field3 )
VALUES 
(1, 'Y', 'N', 'N' ),
(1, 'N', 'N', 'N' ),
(2, 'Y', 'N', 'Y' ),
(2, 'Y', 'N', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' )


--SELECT * FROM @data

; WITH cte
AS (
	SELECT A.id, COUNT(*) AS NbRows
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field1 = 'Y') AS F1
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field2 = 'Y') AS F2
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field3 = 'Y') AS F3
	FROM @data AS A
	GROUP BY A.id
)

SELECT cte.id
FROM cte
WHERE cte.NbRows = cte.F1 OR cte.NbRows = cte.F2 OR cte.NbRows = cte.F3


DELETE FROM @data

INSERT INTO @data ( id, field1, field2, field3 )
VALUES 
(1, 'Y', 'N', 'N' ),
(2, 'Y', 'N', 'Y' ),
(2, 'Y', 'N', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' )


--SELECT * FROM @data

; WITH cte
AS (
	SELECT A.id, COUNT(*) AS NbRows
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field1 = 'Y') AS F1
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field2 = 'Y') AS F2
	, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field3 = 'Y') AS F3
	FROM @data AS A
	GROUP BY A.id
)

SELECT cte.id
FROM cte
WHERE cte.NbRows = cte.F1 OR cte.NbRows = cte.F2 OR cte.NbRows = cte.F3

Open in new window

Scott PletcherSenior DBACommented:
SELECT id
FROM @data
GROUP BY id
HAVING
      MIN(CASE WHEN field1 = 'Y' THEN 1 ELSE 0 END) = 1 OR
      MIN(CASE WHEN field2 = 'Y' THEN 1 ELSE 0 END) = 1 OR
      MIN(CASE WHEN field3 = 'Y' THEN 1 ELSE 0 END) = 1
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
PortletPaulEE Topic AdvisorCommented:
I believe Scott's approach will efficiently solve the question as asked.

I just wanted to show an alternative that would also allow determination of which column has all 'Y's

It starts by "unpivoting" the data (but not by using the "unpivot" command, I find the following to be simpler syntax for that). Once that is complete it sis is just a group by query to complete the task. 
CREATE TABLE mytable(
   Id     INTEGER  NOT NULL
  ,Field1 VARCHAR(4) NOT NULL
  ,Field2 VARCHAR(2) NOT NULL
  ,Field3 VARCHAR(2) NOT NULL
);
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (1,'Y','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (1,'N','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (2,'Y','N','Y');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (2,'Y','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');

Open in new window

select ca.id, ca.all_y
from mytable
cross apply (
    values
      (id, 'field1', field1)
    , (id, 'field2', field2)
    , (id, 'field3', field3)
    ) ca (id, all_y, fieldval)
group by  ca.id, ca.all_y
having min(fieldval) = max(fieldval) and max(fieldval) = 'Y'
;

+-----+--------+
|  id | all_y  |
+-----+--------+
|   2 | field1 |
|   3 | field2 |
+-----+--------+

Open in new window

Heiko BialozytLeiter ITCommented:
hello i would like to recommend a differend possibility.
in case of most records are valid, it's a good idea to search for records which are not matching. In addition then you have a cobination of AND which is faster then OR.

so may be:

SELECT DISTINCT ID from mytable where ID not in (
SELECT ID from mytable where field1 = 'N' AND field2 = 'N' AND field3 = 'N'
)

please check finaly execution plan to see what is your optimal solution.
Good luck
Scott PletcherSenior DBACommented:
If you want to know which column matched, you can adjust my query as follows:

SELECT id,
      CASE WHEN MIN(CASE WHEN field1 = 'Y' THEN 10 ELSE 0 END) = 10 THEN 'Field1'
                WHEN MIN(CASE WHEN field2 = 'Y' THEN 20 ELSE 0 END) = 20 THEN 'Field2'
                WHEN MIN(CASE WHEN field3 = 'Y' THEN 30 ELSE 0 END) = 30 THEN 'Field3'
                END AS which_field_matched
FROM @data
GROUP BY id
HAVING
        MIN(CASE WHEN field1 = 'Y' THEN 10 ELSE 0 END) = 10 OR
        MIN(CASE WHEN field2 = 'Y' THEN 20 ELSE 0 END) = 20 OR
        MIN(CASE WHEN field3 = 'Y' THEN 30 ELSE 0 END) = 30

The CROSS APPLY is also fine.

FYI, the "NOT IN" method will require two full scans of the table; I designed my method to insure that it requires only one table scan, and the CROSS APPLY should require only one table scan also.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.