CODEWITHSUNDEEP
sql-servert-sql
ronniej962
ronniej962

Reputation: 173

SQL Server Query Where Not Exists

I'm have a tough time getting the intended results. I have a table that consist of server name and all disk drive letters associated with that server. I want to query the rows and return a distinct list of server names where a specific drive letter does not exist on that server. So, in this case, I want to know all servers that do not have a "D" drive.

I've tried using a temp table, not exists, left joins between temp tables, not in (select...) from temp table and I'm unable to retrieve a distinct list of servers.

Can someone help me with a query that can to this?

I've included an example table:

CREATE TABLE DRIVES  (
SERVERNAME NVARCHAR(50),
DRIVE NVARCHAR(5)
)

INSERT INTO DRIVES(SERVERNAME, DRIVE)

SELECT '0001', 'C'
UNION
SELECT '0001', 'D'
UNION
SELECT '0001', 'E'
UNION
SELECT '0001', 'F'
UNION
SELECT '0002', 'C'
UNION
SELECT '0002', 'D'
UNION
SELECT '0002', 'E'
UNION
SELECT '0002', 'F'
UNION
SELECT '0003', 'C'
UNION
SELECT '0003', 'E'
UNION
SELECT '0003', 'F'
UNION
SELECT '0004', 'C'
UNION
SELECT '0004', 'D'
UNION
SELECT '0004', 'E'
UNION
SELECT '0004', 'F'
UNION
SELECT '0005', 'C'
UNION
SELECT '0005', 'E'
UNION
SELECT '0005', 'F'

The results should return servers 0003 and 0005 only as not have a "D" drive

Thanks!

Upvotes: 0

Views: 245

Answers (4)

ronniej962
ronniej962

Reputation: 173

This is what I did to get the results I was looking for:

CREATE TABLE DRIVES (
    SERVERNAME NVARCHAR(50),
    DRIVE NVARCHAR(255)
)

INSERT INTO DRIVES(SERVERNAME,DRIVE)

SELECT DISTINCT
    tt.SystemName00,
    STUFF (( SELECT DISTINCT ',  ' + SUB.[deviceid00] AS [text()] 
                FROM [SMS].dbo.Logical_Disk_DATA SUB
                WHERE SUB.[SystemName00] = tt.[SystemName00]
                FOR XML PATH('') ), 1, 1, '')
FROM [SMS].dbo.Logical_Disk_DATA tt
ORDER BY tt.SystemName00

select *
from DRIVES
where DRIVE not like '%D:%'

Upvotes: 2

Iłya Bursov
Iłya Bursov

Reputation: 24146

I'd use something like that:

select distinct SERVERNAME
from DRIVES
where SERVERNAME not in (select SERVERNAME from DRIVES where DRIVE = 'D')

Fiddle: http://sqlfiddle.com/#!18/d1573/3

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try this . This will give you data if one of the drive is not there.

SELECT SERVERNAME 
FROM DRIVES
WHERE DRIVE IN ( 'C','D','E','F' )
GROUP BY SERVERNAME
HAVING (  COUNT( DISTINCT DRIVE) ) < 4

OUTPUT

SERVERNAME
--------------------------------------------------
0003
0005

(2 rows affected)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

One approach uses conditional aggregation:

SELECT SERVERNAME
FROM DRIVES
GROUP BY SERVERNAME
HAVING COUNT(CASE WHEN DRIVE = 'D' THEN 1 END) = 0;

Demo

Upvotes: 0

Related Questions