D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Tuesday, 4 January 2011

Using the EXCEPT clause wrapped in a CTE - SQL

--
--example EXCEPT clause wrapped in CTE
--
WITH RogueEstab
AS
(
SELECT EstablishmentID FROM Establishment
EXCEPT
SELECT JobLog_EstablishmentID FROM JobLog
)
SELECT RE.EstablishmentID, E.EstablishmentName FROM RogueEstab AS RE
INNER JOIN Establishment AS E
ON RE.EstablishmentID = E.EstablishmentID

1 comment:

chichichi said...

Highly recommended for people just getting started on SQL selects.