Simple Not Exists question

Question

Simple Not Exists question

Keith Edwards

Default port

Points: 1446
More actions
January 30, 2002 at 6:14 am

#257934

I am trying to display the different records between two tables that have nine fields that are identical. I tried something like the code below, but returned zero records. Any suggestions would be greatly appreciated. thanks
select * from cmsemployeetime where emptim_dateworked Between '01/06/2002' and '01/19/2002' and emptim_employeeid IN (select distinct emp_employeeid from cmsemployee where emp_paycycle = 'A' and emp_active = 'Y') and Not Exists (select * from cmsemployeetimecompare)

Viewing 9 posts - 1 through 8 (of 8 total)

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

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 1

The way this is written, the only way the where clause is going to evaluate to true is if there are no records present in (select * from cmsemployeetimecompare). If there's a single record, Not Exists is False. Since you are using AND, that makes the whole where clause false.

So that I can better understand, are you trying to find the records that exist in cmsemployeetime that do not have a match in cmsemployeetimecompare?

Also, could you post the basic structure of the tables involved (just the relevant fields would be fine). It looks like if we use JOINs we can solve the issue.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Keith Edwards Default port Points: 1446 More actions · Answer 2

Brian,

Yes, I am trying to find the records that exist in cmsemployeetime that do not have

a match in cmsemployeetimecompare. Below is the relevant field structure for cmsemployeetime. cmsemployeetimecompare has an identical structure. Thanks for your help with this.

cmsEmployeeTime

[EMPTIM_EmployeeID] [varchar] (11) NOT NULL

[EMPTIM_DateWorked] [datetime] NOT NULL

[EMPTIM_WorkPlan] [varchar] (6) NOT NULL

[EMPTIM_ActivityCode] [varchar] (5) NOT NULL

[EMPTIM_Type] [smallint] NOT NULL

[EMPTIM_Description] [varchar] (45) NULL

[EMPTIM_HoursWorked] [float] NOT NULL

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 3

Are you looking for an identical match across all fields or are you just looking for a match based on primary key? If it is a match on the primary key, does this make up the PK, or are there other columns:

[EMPTIM_EmployeeID] [varchar] (11) NOT NULL

[EMPTIM_DateWorked] [datetime] NOT NULL

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Keith Edwards Default port Points: 1446 More actions · Answer 4

I am looking for an identical match across all fields.

Keith

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 5

If you do a left outer join on all fields and look for nulls in any of the key fields in cmsemployeetimecompare this should work.

Paul Ibison

Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 6

Paul and I are thinking along the same lines:

SELECT ET.*

FROM cmsEmployeeTime ET

JOIN cmsEmployee E

ON ET.EMPTIM_EmployeeID = E.EMP_EmployeeID

LEFT JOIN cmsEmployeeTimeCompare ETC

ON ET.EMPTIM_EmployeeID = ETC.EMPTIM_EmployeeID

AND ET.EMPTIM_DateWorked = ETC.EMPTIM_DateWorked

AND ET.EMPTIM_WorkPlan = ETC.EMPTIM_WorkPlan

AND ET.EMPTIM_ActivityCode = ETC.EMPTIM_ActivityCode

AND ET.EMPTIM_Type = ETC.EMPTIM_Type

AND ET.EMPTIM_Description = ETC.EMPTIM_Description

AND ET.EMPTIM_HoursWorked = ETC.EMPTIM_HoursWorked

WHERE ET.EMPTIM_DateWorked BETWEEN '01/06/2002' AND '01/19/2002'

AND E.EMP_PayCycle = 'A'

AND E.EMP_Active = 'Y'

AND ETC.EMPTIM_EmployeeID IS NULL

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Keith Edwards Default port Points: 1446 More actions · Answer 7

Thanks for the help on this Brian and your quick responses. This works great.

Keith

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 8

Glad to be of service.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley