Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieve when all values in group have same record

Author  Topic 

Hammerklavier
Starting Member

26 Posts

Posted - 2015-02-25 : 10:30:14
Hi,

Here is some sample data:


CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)
)

INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (1,1,NULL)
INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (2,1,'')
INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (3,1,'ABC')
INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (4,2,'')
INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (5,2,'XYZ')
INSERT INTO #MyTable (Pk, GroupID, Value) VALUES (6,3,NULL)

SELECT * FROM #MyTable

DROP TABLE #MyTable


I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.

What would be the most efficient way to write such a query?

I greatly appreciate any assistance!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-25 : 10:39:07
[code]
SELECT *
FROM #MyTable T1
WHERE NOT EXISTS
(
SELECT 1
FROM #MyTable T2
WHERE T2.GroupID = T1.GroupID
AND COALESCE(T2.Value, '') NOT IN ('','XYZ')
);
[/code]
Go to Top of Page

Hammerklavier
Starting Member

26 Posts

Posted - 2015-02-25 : 12:18:48
Thanks Ifor. I will give this a try!
Go to Top of Page
   

- Advertisement -