2 Replies Latest reply on Jul 2, 2007 4:02 PM by Nat5

    Problem Query

      Here's what I'm trying to do.

      query A select * employees who have signed in today.

      query B selects all employees who should sign in by job code and active status.

      these both give correct results when run independently.

      Now I'm trying to compare the two tables to see who hasn't signed in today by querying the queries. I can easily get everyone who has signed in but I have tried every way I can find to get who hasn't signed in. I keep coming back to this which doesn't work:

      <cfquery name="remaining" dbtype="query">
      Select queryB.LastName, QueryBFirstName, queryB.employeeID,QueryA.EmpID
      FROM QueryA,QueryB
      WHERE queryA.employeeID <> QueryB.empid

      Will someone please give me some help on this? I've tried so many ways my brain is in a never ending loop :)
        • 1. Re: Problem Query
          efecto747 Level 1
          You can do this with just one query - no need to use query of query (best avoided whenever possible).

          Just modify query B using the NOT IN argument to select all employees who should sign in by job code and active status and who are NOT in query A.

          It would look something like this:

          SELECT *
          FROM employees
          WHERE jobCode = 'abc'
          AND activeStatus = 'xyz'
          AND employeeID NOT IN (
          SELECT employeeID FROM employees WHERE <insert date comparison code here>

          hth - cheers.
          • 2. Re: Problem Query
            Nat5 Level 1

            My queries were from two different data sources so I had to make temp tables first and then use the suggested code. Works great! Thanks...