Thanks Al! :) That worked nicely.... Thank you very much *nod* *nod*
I was thinking something else though... can't subqueries take the place of all those different joins then, if one wanted to use them for some reason? -Gel -----Original Message----- From: Everett, Al [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:04 AM To: CF-Talk Subject: RE: CFQUERY: All employees who haven't entered data... Use a subquery: <CFQUERY Name="GetExceptions" datasource="#DSN#"> SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees WHERE tblEmployees.ID NOT IN ( SELECT tblMaster.empid FROM tblMaster WHERE tblMaster.startdate <= #CreateODBCDate(enddate)# AND tblMaster.startdate >= #CreateODBCDate(startdate)# ) </CFQUERY> > 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? ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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
