Avatar of Bharat Guru
Bharat Guru
 asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Jim Horn

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 Moreau

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 Pletcher

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

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 Bialozyt

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
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question