0

I have two tables, TABLE1 and TABLE2, each containing the total amount of cash bills in a cash register for a given day in a given building. Each table is a data snapshot taken at a certain time during the day.

I want to list only the rows on which there is a difference in the amount of bills (any bill difference) in the same cash register for the same building for the same date. Cash register IDs are only unique for the building in which they are contained.

The purpose is to find out which cash registers in which buildings contained a different amount of bills during the later snapshot (TABLE2) for the same day.

The data structure of both TABLE1 and TABLE2 is:

Column  Type    Comment 
ID  NUMBER(10,0) -- Serial 
DAY DATE(7) -- The date  
BUILDING_ID NUMBER(10,0) -- The ID of the building 
REGISTER_ID NUMBER(10,0) -- The ID of the cash register (unique per building ID) 
BILL5   NUMBER(10,0) -- The number of 5 dollar bills 
BILL10  NUMBER(10,0) -- The number of 10 dollar bills 
BILL20  NUMBER(10,0) -- The number of 20 dollar bills 
BILL50  NUMBER(10,0) -- The number of 50 dollar bills
Werner
  • 3,523
  • 1
  • 22
  • 21

2 Answers2

2
SELECT * 
FROM table1 t1 
JOIN table2 t2
  -- Use these critieria to match rows from t1 with rows from t2
  ON ( t1.building_id = t2.building_id 
    AND t1.register_id = t2.register_id 
    AND t1.day = t2.day )
-- Return only rows where the amount of bills in t1 and t2 is different
WHERE  t1.bill5 <> t2.bill5 
        OR t1.bill10 <> t2.bill10 
        OR t1.bill20 <> t2.bill20 
        OR t1.bill50 <> t2.bill50
Thomas Aregger
  • 520
  • 3
  • 8
1

You can try something like this. Each table is scanned just once, and there are no joins. Select from both tables and add a column for "source"; group by day, building_id and register_id and the bill counts - and retain only the groups for which the count is 1. If the bill counts are identical, the row count within the group will be 2, so the rows will not be returned; but if the bill counts are different, the rows will not be grouped together; the row count for each will be 1, and they will be returned. For these rows (groups with exactly 1 row), the MAX() in SELECT has no effect (but it is still needed since it's a GROUP BY query).

select   max(source) as source, day, building_id, register_id, bill5, bill20, bill50
from     (
           select 'table1' as source, day, building_id, register_id, bill5, bill20, bill50
             from table1
           union all
           select 'table2' as source, day, building_id, register_id, bill5, bill20, bill50
             from table2
         )
group by day, building_id, register_id, bill5, bill20, bill50
having   count(*) = 1
order by day, building_id, register_id, source
;
  • This query only gets all records that are only present in one of the two tables. – Thorsten Kettner Feb 07 '17 at 21:09
  • @ThorstenKettner - oops, the bill counts must also be in the GROUP BY. I will edit to make the correction, thank you for pointing it out! –  Feb 07 '17 at 21:26