use subquery. either NOT IN or WHERE NOT EXISTS On Mon, 30 Sep 2002, Angel Stewart wrote:
> Hey all, > > Is there a simple SQL statement that would allow me to select all the > employees...or all the EmpIds that are NOT in the Master table.. > > So I have tblEmployees and tblMaster. > > Basically I want to display all the bad bad people that did not enter > their time for a specific period...an exception report. > > I can do this easily without factoring a time period, but once I put the > Time Period in..it no longer works.. > > Without Time Period: > > <CFQUERY Name="GetExceptions" datasource="#DSN#"> > > SELECT tblEmployees.EmpName, tblEmployees.DeptID > FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = > tblMaster.empid > WHERE tblMaster.empid Is Null > </CFQUERY> > > This just shows Employees who have never entered time at all. That isn't > exactly what I want. > > With Time Period > > <CFQUERY Name="GetExceptions" datasource="#DSN#"> > > SELECT tblEmployees.EmpName, tblEmployees.DeptID > FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = > tblMaster.empid > WHERE > tblMaster.startdate <= #CreateODBCDate(enddate)# > AND tblMaster.startdate >= #CreateODBCDate(startdate)# > AND tblMaster.empid Is Null > </CFQUERY> > > I want this to show Employees that haven't entered time for a specific > period... > > I know my SQL is confused somewhere along the line...can anyone help? > > -Angel > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
