where exists and where not exists in sql rewrite as single query in sql server

Question

Post reply

where exists and where not exists in sql rewrite as single query in sql server

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
March 4, 2014 at 6:12 am

#396363

hi All,
i have a query like this.
i want to rewrite as single query use WHERE EXISTS and NOT EXISTS .
CREATE VIEW [dbo].[job_refreshCompanyCountExtended_vw]
AS
SELECT TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
max(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,
max(CASE WHEN symbolTypeid = 6 and primaryFlag = 1 THEN symbolValue ELSE NULL END) AS dunsNumber
FROM dbo.Symbol_tbl s (nolock)
INNER JOIN dbo.company_tbl (nolock)
ON dbo.company_tbl.companyId = s.objectId
AND (dbo.company_tbl.userCompanyId = s.userCompanyId OR dbo.company_tbl.userCompanyId=0)
where exists (select * from Symbol_tbl _s (nolock)
where _s.objectId = s.objectId
and _s.symbolTypeId = 6
and _s.primaryFlag = 1)
group by s.objectid
order by s.objectid
UNION
SELECT TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
max(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,
max(CASE WHEN symbolTypeid = 6 THEN symbolValue ELSE NULL END) AS dunsNumber
FROM dbo.Symbol_tbl s (nolock)
INNER JOIN dbo.company_tbl (nolock)
ON dbo.company_tbl.companyId = s.objectId
AND (dbo.company_tbl.userCompanyId = s.userCompanyId OR dbo.company_tbl.userCompanyId=0)
where not exists (select * from Symbol_tbl _s (nolock)
where _s.objectId = s.objectId
and _s.symbolTypeId = 6
and _s.primaryFlag = 1)
group by s.objectid
order by s.objectid
;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT --TOP 100 PERCENT

objectid AS companyid,

-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,

MAX(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,

MAX(CASE WHEN symbolTypeid = 6 and primaryFlag = 1 THEN symbolValue ELSE NULL END) AS dunsNumber,

x.MyBool

FROM dbo.Symbol_tbl s (nolock)

INNER JOIN dbo.company_tbl c (nolock)

ON c.companyId = s.objectId

AND (c.userCompanyId = s.userCompanyId OR c.userCompanyId=0)

CROSS APPLY (

SELECT MyBool = CASE WHEN EXISTS (

SELECT 1 FROM dbo.Symbol_tbl _s (nolock)

WHERE _s.objectId = s.objectId

AND _s.symbolTypeId = 6

AND _s.primaryFlag = 1) THEN 1 ELSE 0 END

) x

GROUP BY s.objectid, x.MyBool

--ORDER BY s.objectid, x.MyBool

-- don't use TOP 100 PERCENT ...ORDER BY in a view definition.

-- In recent versions of SQL Server it's ignored (try figuring that out when you upgrade), in later versions it may raise an error

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden