Link to home
Start Free TrialLog in
Avatar of bganoush
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?
Avatar of slightwv (䄆 Netminder)
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?
intersect is east but I'm not sure what you are wanting:

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

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, exactly... thanks.
Think you can intersect by using the same alias for the intersect columns, right?
gerwinjansen,

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.
@slightvw, I see, I remembered such a query, it must have had a to_char somewhere in it, thanks anyway :)