Avatar of bganoush
 asked on

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

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?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
slightwv (䄆 Netminder)

>>The "lastid" is the id of the last employee added to emplist

Bad design.  Forget writing code to check for this.  Change the design.

Can I ask what requirement this column is supposed to solve?
slightwv (䄆 Netminder)

intersect is east but I'm not sure what you are wanting:

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

intersect won't work... i need to compare max(empid) with lastid... intersect only works when the field names are the same...

As for the design, I can't change it. The second table is in another schema that has different security privileges than the first schema and the table in the other schema is shared using a synonym.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Yes, exactly... thanks.
Gerwin Jansen

Think you can intersect by using the same alias for the intersect columns, right?
slightwv (䄆 Netminder)


Column names don't matter for the set operators.  Only data types.

The problem was they wanted to perform a validation between two tables whre the values didn't match.  The intersect would return the values that did match.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gerwin Jansen

@slightvw, I see, I remembered such a query, it must have had a to_char somewhere in it, thanks anyway :)