troubleshooting Question

Need to check if one table contains the max id from another table...

Avatar of bganoush
bganoush asked on
Oracle DatabaseSQL
8 Comments1 Solution480 ViewsLast Modified:
I have two tables, "emplist" and "deptinfo".

In the first table "emplist" is a list of employees as follows:

TABLE emplist
   empid NUMBER,
   dept VARCHAR(2)
   empname VARCHAR(20),
   empid NUMBER

Then there is a smaller list "deptinfo" where there is one entry per department as follows:

TABLE deptinfo
   deptid NUMBER,
   deptcode VARCHAR(2),
   lastid NUMBER
   
The "lastid" is the id of the last employee added to emplist. So normally max(empid) in table emplist should always match "lastid" in table depinfo for deptinfo.deptcode == emplist.dept.

To verify this, I run these two SQL statements which will generate two lists then I visually compare the two lists to make sure the "lastid" of the second table matches the max(empid) of the first table.

select dept, max(empid) from emplist group by dept order by dept;
select deptcode,lastid from deptinfo ORDER BY team;

My question is…  How can I write a SQL statement that will give the intersection of the two tables when emplist.max(empid) .NE. deptinfo.lastid?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros