how to compare 2 tables oracle database

user_10174861 0
Viewed 37

Table 1

where we have ID as primary key and his own salary

ID Name Salary
1 " X " 500
2 " Y " 1000
3 " Z " 1500

table 2

where we have data from the payment system

ID Date Salary
1 " 6/22/2020 " 500
2 " 6/25/2020 " 1000
3 " 8/05/2021 " 1500

i want a query to compare items from table 2 with table 1 where my goal is to make sure every employee gets his exact salary paid as it in table 1

example

employee "1" with salary 500 let say on a month he received only 300 want to retrieve employee

* preferred : calculate how many months out of his employment time where he didn't get his exact salary and calculate how much difference $

* Note : the employee might get paid less or more than his salary

toad oracle11g sql database
2 Answers

Something like this should do it:

select
  t1.salary as t1_salary,
  t2.*
from 
  t1 left outer join t2 on t1.id = t2.id
user_26705710

Join tables on ID and put the difference into the where clause.

Sample data (you have it & you don't have to type that):

SQL> with
  2  table1 (id, name, salary) as
  3    (select 1, 'x',  500 from dual union all
  4     select 2, 'y', 1000 from dual
  5    ),
  6  table2 (id, datum, salary) as
  7    (select 1, date '2022-01-01',  500 from dual union all
  8     select 1, date '2022-02-01',  300 from dual union all
  9     select 1, date '2022-03-01',  500 from dual union all
 10     select 1, date '2022-04-01',  700 from dual union all
 11     select 2, date '2022-01-01', 1000 from dual union all
 12     select 2, date '2022-02-01', 1000 from dual union all
 13     select 2, date '2022-03-01',  900 from dual union all
 14     select 2, date '2022-04-01', 1200 from dual
 15    )

Query begins here:

 16  select a.id,
 17    a.name,
 18    b.datum,
 19    b.salary - a.salary as difference
 20  from table1 a join table2 b on a.id = b.id
 21  where b.salary - a.salary <> 0;

        ID NAME DATUM      DIFFERENCE
---------- ---- ---------- ----------
         1 x    01.02.2022       -200
         1 x    01.04.2022        200
         2 y    01.03.2022       -100
         2 y    01.04.2022        200

SQL>
user_90979060
Related