NOT EXISTS query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

NOT EXISTS query

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-07 : 16:16:08

I have gone in circles hour after hour with this. I just cannot get it. I have 2 queries here, the first of which has 2500 rows. The second has 200 rows which is a subset of the 2500. All I want to do is get a query that has THE OTHER 2300 in its results. Either I get errors, or I get no rows, but never the 2300. Can someone show me how to combine these 2 so I can get the rows that DO NOT EXIST in the sub-query? The EDITED_ON_EMPLID is supposed to match EMPLID. They are just 11 digit number strings which consist of 5 leading zeroes, ie: '00000123456' I know this must be dreadfully simple, but I have had a mental block and can't get it.

SELECT D.EDITED_ON_EMPLID
-- Returns 2500 rows when run without the sub-query below
, CASE When NULLIF(D.LASTNAME,'') IS NULL THEN H.LAST_NAME ELSE D.LastName END LAST_NAME
, CASE When NULLIF(D.FIRSTNAME,'') IS NULL THEN H.FIRST_NAME ELSE D.FirstName END FIRST_NAME
, CASE When NULLIF(D.Email,'') IS NULL THEN H.EMAIL_ID ELSE D.Email END EMAIL
, D.LASTNAME ON_LASTNAME
, D.FIRSTNAME ON_FIRSTNAME
, D.EMAIL, H.LAST_NAME HR_LAST_NAME, H.FIRST_NAME HR_FIRST_NAME, H.EMAIL_ID HR_EMAIL,
D.WB_ID, D.LastName, D.FirstName, D.Email, D.PrimaryUser
FROM (
SELECT DISTINCT
	  X.[EMPLID]
	  ,Case when Nullif(X.[EMPLID],'') IS NULL THEN NULLIF(O.[EMPLID],'') ELSE NULLIF(X.[EMPLID],'') END EDITED_ON_EMPLID
      ,O.[EMPLID] ON_EMPLID
      ,Case When NullIf(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)),'') IS NOT NULL
		   Then [PrimaryUser] 
		   Else RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1))
	   End WB_LOGON_ID
      ,O.[WB_ID]
      ,O.[PrimaryUser]
      ,O.[UniqueUserName]
      ,O.[Email]
      ,O.[FirstName]
      ,O.[LastName]
      ,O.[UserManager]
      ,O.[REPORTS_TO_MGR_NAME]
      ,O.[REPORTS_TO_MGR_EMAIL]
      ,O.[REPORTS_TO_MGR_EMPLID]
  FROM [SoftwareReporting].[dbo].[vOneNoteUsers] O
  LEFT join WB_WF_EMPLID_XREF X ON X.WB_LOGON_ID = O.WB_ID
  ) D
  LEFT JOIN HR_NSV_PM H ON H.EMPLID = D.EMPLID
  
  -- No rows returned for NOT exists. If I change NOT EXISTS to EXISTS, all 2500 rows are returned
  WHERE NOT EXISTS
(
	-- sub-query here returns 200 records
	select DISTINCT *
	from OneNoteUsersPM O
	inner join MSDN_AllData MSDN on MSDN.EMPLID = O.EMPLID
	union
	select DISTINCT *
	from OneNoteUsersPM O
	inner join MSDN_Alldata MSDN ON MSDN.Email = O.Email
	-- This line is what I tried to use to match to the outer query and have tried many
	-- different variations of
	where O.EMPLID = D.EDITED_ON_EMPLID
	-- end of sub-query
)
GO

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-07 : 16:29:09

Try this

SELECT D.EDITED_ON_EMPLID
-- Returns 2500 rows when run without the sub-query below
, CASE When NULLIF(D.LASTNAME,'') IS NULL THEN H.LAST_NAME ELSE D.LastName END LAST_NAME
, CASE When NULLIF(D.FIRSTNAME,'') IS NULL THEN H.FIRST_NAME ELSE D.FirstName END FIRST_NAME
, CASE When NULLIF(D.Email,'') IS NULL THEN H.EMAIL_ID ELSE D.Email END EMAIL
, D.LASTNAME ON_LASTNAME
, D.FIRSTNAME ON_FIRSTNAME
, D.EMAIL, H.LAST_NAME HR_LAST_NAME, H.FIRST_NAME HR_FIRST_NAME, H.EMAIL_ID HR_EMAIL,
D.WB_ID, D.LastName, D.FirstName, D.Email, D.PrimaryUser
FROM (
SELECT DISTINCT
	  X.[EMPLID]
	  ,Case when Nullif(X.[EMPLID],'') IS NULL THEN NULLIF(O.[EMPLID],'') ELSE NULLIF(X.[EMPLID],'') END EDITED_ON_EMPLID
      ,O.[EMPLID] ON_EMPLID
      ,Case When NullIf(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)),'') IS NOT NULL
		   Then [PrimaryUser] 
		   Else RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1))
	   End WB_LOGON_ID
      ,O.[WB_ID]
      ,O.[PrimaryUser]
      ,O.[UniqueUserName]
      ,O.[Email]
      ,O.[FirstName]
      ,O.[LastName]
      ,O.[UserManager]
      ,O.[REPORTS_TO_MGR_NAME]
      ,O.[REPORTS_TO_MGR_EMAIL]
      ,O.[REPORTS_TO_MGR_EMPLID]
  FROM [SoftwareReporting].[dbo].[vOneNoteUsers] O
  LEFT join WB_WF_EMPLID_XREF X ON X.WB_LOGON_ID = O.WB_ID
  ) D
  LEFT JOIN HR_NSV_PM H ON H.EMPLID = D.EMPLID
  LEFT JOIN 
(
	select DISTINCT *
	from OneNoteUsersPM O
	inner join MSDN_AllData MSDN on MSDN.EMPLID = O.EMPLID
	union
	select DISTINCT *
	from OneNoteUsersPM O
	inner join MSDN_Alldata MSDN ON MSDN.Email = O.Email
) T
ON T.EMPLID = D.EDITED_ON_EMPLID
WHERE T.EMPLID IS NULL

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-07 : 16:51:42

Thank you for the reply. It looks good, but I am getting the following error when I run it: 'The column 'Email' is specified multiple times for 'T'. I even stripped out references to that column and stripped out the second half of the sub-query (the UNION part) and I still get the error:

SELECT D.EDITED_ON_EMPLID
-- Returns 2500 rows when run without the sub-query below
, CASE When NULLIF(D.LASTNAME,'') IS NULL THEN H.LAST_NAME ELSE D.LastName END LAST_NAME
, CASE When NULLIF(D.FIRSTNAME,'') IS NULL THEN H.FIRST_NAME ELSE D.FirstName END FIRST_NAME
--, CASE When NULLIF(D.Email,'') IS NULL THEN H.EMAIL_ID ELSE D.Email END EMAIL
, D.LASTNAME ON_LASTNAME
, D.FIRSTNAME ON_FIRSTNAME
--, H.LAST_NAME HR_LAST_NAME, H.FIRST_NAME HR_FIRST_NAME, H.EMAIL_ID HR_EMAIL, D.WB_ID, D.LastName, D.FirstName, D.PrimaryUser
FROM (
SELECT DISTINCT
	  X.[EMPLID]
	  ,Case when Nullif(X.[EMPLID],'') IS NULL THEN NULLIF(O.[EMPLID],'') ELSE NULLIF(X.[EMPLID],'') END EDITED_ON_EMPLID
      ,O.[EMPLID] ON_EMPLID
      ,Case When NullIf(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)),'') IS NOT NULL
		   Then [PrimaryUser] 
		   Else RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1))
	   End WB_LOGON_ID
      ,O.[WB_ID]
      ,O.[PrimaryUser]
      ,O.[UniqueUserName]
--      ,O.[Email]
      ,O.[FirstName]
      ,O.[LastName]
      ,O.[UserManager]
      ,O.[REPORTS_TO_MGR_NAME]
      ,O.[REPORTS_TO_MGR_EMAIL]
      ,O.[REPORTS_TO_MGR_EMPLID]
  FROM [SoftwareReporting].[dbo].[vOneNoteUsers] O
  LEFT join WB_WF_EMPLID_XREF X ON X.WB_LOGON_ID = O.WB_ID
  ) D
  LEFT JOIN HR_NSV_PM H ON H.EMPLID = D.EMPLID
  LEFT JOIN 
(
	select DISTINCT *
	from OneNoteUsersPM O
	inner join MSDN_AllData MSDN on MSDN.EMPLID = O.EMPLID
--	union
--	select DISTINCT *
--	from OneNoteUsersPM O
--	inner join MSDN_Alldata MSDN ON MSDN.Email = O.Email
) T
ON T.EMPLID = D.EDITED_ON_EMPLID
WHERE T.EMPLID IS NULL

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-07 : 17:05:11

I finally got it by taking the SELECT * out of the sub-query and replacing it with O.EMPLID. I have my 2300 rows now and I do thank you for the help. I wish I understood it better. And I am not sure if what I did was entirely sound or not but it seems to have worked in this case. Here is what I ended up with:

SELECT D.EDITED_ON_EMPLID
, CASE When NULLIF(D.LASTNAME,'') IS NULL THEN H.LAST_NAME ELSE D.LastName END LAST_NAME
, CASE When NULLIF(D.FIRSTNAME,'') IS NULL THEN H.FIRST_NAME ELSE D.FirstName END FIRST_NAME
, CASE When NULLIF(D.Email,'') IS NULL THEN H.EMAIL_ID ELSE D.Email END EMAIL
, D.LASTNAME ON_LASTNAME
, D.FIRSTNAME ON_FIRSTNAME
, H.LAST_NAME HR_LAST_NAME, H.FIRST_NAME HR_FIRST_NAME, H.EMAIL_ID HR_EMAIL, D.WB_ID, D.LastName, D.FirstName, D.PrimaryUser
FROM (
SELECT DISTINCT
	  X.[EMPLID]
	  ,Case when Nullif(X.[EMPLID],'') IS NULL THEN NULLIF(O.[EMPLID],'') ELSE NULLIF(X.[EMPLID],'') END EDITED_ON_EMPLID
      ,O.[EMPLID] ON_EMPLID
      ,Case When NullIf(RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1)),'') IS NOT NULL
		   Then [PrimaryUser] 
		   Else RIGHT(PrimaryUser,LEN(PrimaryUser)- charindex('\',PrimaryUser,1))
	   End WB_LOGON_ID
      ,O.[WB_ID]
      ,O.[PrimaryUser]
      ,O.[UniqueUserName]
      ,O.[Email]
      ,O.[FirstName]
      ,O.[LastName]
      ,O.[UserManager]
      ,O.[REPORTS_TO_MGR_NAME]
      ,O.[REPORTS_TO_MGR_EMAIL]
      ,O.[REPORTS_TO_MGR_EMPLID]
  FROM [SoftwareReporting].[dbo].[vOneNoteUsers] O
  LEFT join WB_WF_EMPLID_XREF X ON X.WB_LOGON_ID = O.WB_ID
  ) D
  LEFT JOIN HR_NSV_PM H ON H.EMPLID = D.EMPLID
  LEFT JOIN 
(
	select DISTINCT O.EMPLID
	from OneNoteUsersPM O
	inner join MSDN_AllData MSDN on MSDN.EMPLID = O.EMPLID
	union
	select DISTINCT O.EMPLID
	from OneNoteUsersPM O
	inner join MSDN_Alldata MSDN ON MSDN.Email = O.Email
) T
ON T.EMPLID = D.EDITED_ON_EMPLID
WHERE T.EMPLID IS NULL

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 10:30:43

Np. Glad you got it figured.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources