Tuesday, July 1, 2014

SQL query using not equal to or not exists

E.g.

DECLARE @Sample TABLE
(
ClientName VARCHAR(255) ,
Outputs VARCHAR(255)
);

INSERT INTO @Sample
VALUES ( 'ABC', 'SOSP' ),
( 'ABC', 'Pre Start' ),
( 'ABC', 'BA' ),
( 'XYZ', 'SOSP' ),
( 'XYZ', 'BA' ),
( 'DEF', 'SOSP' ),
( 'DEF', 'BE' ),
( 'QWE', 'SOSP' ),
( 'QWE', 'Pre Start' );


SELECT S.ClientName
FROM @Sample S
EXCEPT
SELECT S.ClientName
FROM @Sample S
WHERE S.Outputs IN ( 'BA', 'Pre Start' );

SELECT DISTINCT
S.ClientName
FROM @Sample S
WHERE S.ClientName NOT IN ( SELECT S.ClientName
FROM @Sample S
WHERE S.Outputs IN ( 'BA', 'Pre Start' ) );


No comments:

Post a Comment