Reputation: 173
I have a table with 2 columns - server_name, status_name. I'm trying to target specific server/status combinations
Example:
server_name status_name
server01 decommissioned
server01 archive
server02 decommissioned
server02 production
server03 decommissioned
server03 test
server04 decommissioned
server04 archive
I want to return only the server/status combination of decommissioned/archive
Results:
server_name status_name
server01 decommissioned
server01 archive
server04 decommissioned
server04 archive
I've tried using a cursor but because at least one set of status_name is decommissioned, it returns all rows.
Is there a way I can retrieve only the pair status_name I'm looking for?
CREATE TABLE TEST (
SERVER_NAME nvarchar(50),
STATUS_NAME nvarchar(50)
)
INSERT INTO TEST (SERVER_NAME, STATUS_NAME)
VALUES(N'SERVER01', N'DECOMMISSIONED'), (N'SERVER01', N'ARCHIVE'),
(N'SERVER02', N'DECOMMISSIONED'), (N'SERVER02', N'PRODUCTION'),
(N'SERVER03', N'DECOMMISSIONED')
,(N'SERVER03', N'ARCHIVE'), (N'SERVER04', N'DECOMMISSIONED'),
(N'SERVER04', N'TEST')
DECLARE DECOM_Cursor CURSOR FOR
SELECT [SERVER_NAME],
[STATUS_NAME]
FROM TEST
WHERE [STATUSNAME] IN ('DECOMMISSIONED', 'ARCHIVE');
OPEN DECOM_Cursor;
FETCH NEXT FROM DECOM_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM DECOM_Cursor;
END;
CLOSE DECOM_Cursor;
DEALLOCATE DECOM_Cursor;
GO
Upvotes: 1
Views: 46
Reputation: 6018
I know this question has already been answered, but my answer is a little less complicated than M.Ali's. I don't have a subquery for each value. Secondly, because it doesn't have to access the table twice(once for each subquery), it seems to perform better than M.Ali's solution. I was getting less execution time and less logical reads when I ran my query versus his. Of course, it may different for your full data set, but I would recommend trying my query out.
SELECT *
FROM Test A
CROSS APPLY (
SELECT COUNT(STATUS_NAME)
FROM Test B
WHERE A.SERVER_NAME = B.SERVER_NAME
AND B.STATUS_NAME IN ('DECOMMISSIONED', 'ARCHIVE')
GROUP BY B.SERVER_NAME
) CA(match_cnt)
WHERE match_cnt = 2
Upvotes: 0
Reputation: 69594
SELECT *
FROM TableName t
WHERE EXISTS (SELECT 1
FROM TableName
WHERE t.Server_name = Server_Name
AND Status_name = 'decommissioned')
AND EXISTS (SELECT 1
FROM TableName
WHERE t.Server_name = Server_Name
AND Status_name = 'archive')
Upvotes: 1