• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Problem Query

New Here ,
Jun 25, 2007 Jun 25, 2007

Copy link to clipboard

Copied

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
</cfquery>

Will someone please give me some help on this? I've tried so many ways my brain is in a never ending loop 🙂
Thanks!
TOPICS
Advanced techniques

Views

178

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 25, 2007 Jun 25, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 02, 2007 Jul 02, 2007

Copy link to clipboard

Copied

LATEST

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...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation