Mysql Question posted in
The official MySQL documentation can be found here.

MySQL to show difference in values in 2 similar tables

I have table as follows (let’s call it old data).

UNIQUE_PJT PROJECT JOB_TITLE SALARY_A SALARY_B
IT_9810_PR IT_9810 Programmer 10000 6000
IT_9810_AC IT_9810 Accountant 9000 4500
IT_9810_SPR IT_9810 Programmer Sr 15000 8000
IT_9810_SR IT_9810 Sales Representative 12000 9000
  • This data extends to thousand of rows with each Project having upto 100 rows.
  • UNIQUE_PJT is unique and can be used to compare data in the 2 tables.

Now I have newer salary data values in ditto same structure. Newer data facts:

  • Some Project have been added, some have been removed
  • Some Job Title have been added, some have been removed

new data table:

UNIQUE_PJT PROJECT JOB_TITLE SALARY_A SALARY_B
IT_9810_AC IT_9810 Accountant 5000 8500
IT_9810_SPR IT_9810 Programmer Sr 25000 12000
IT_9810_SR IT_9810 Sales Representative 20000 15000

I want to show the changes in new data based on old data. Display pages are project and salary class specific. So, desired output is for Project IT_9810 and salary class SALARY_A:-

PROJECT JOB_TITLE SALARY_A
IT_9810 Programmer 10000 (·)
IT_9810 Accountant 5000 (-4000)
IT_9810 Programmer Sr 25000 (+10000)
IT_9810 Sales Representative 20000 (+8000)

What should be the best approach for this.

I tried keeping the old data as table_old and newer data as table_new. Now on project display page, I selected all values corresponding to a Project from table_new and for each result row of –UNIQUE_PJT, queried table_old. But this is very slow since it queries DB again and again.

Tags:

2

Answers


  1. 0

    You should use a single query that joins the old and new data tables based on the UNIQUE_PJT field.

    SELECT 
    new.PROJECT, 
    new.JOB_TITLE, 
    new.SALARY_A,
    CONCAT(new.SALARY_A, 
           CASE 
               WHEN old.SALARY_A IS NULL THEN ' (·)' 
               ELSE CONCAT(' (', 
                           CASE 
                               WHEN new.SALARY_A - old.SALARY_A > 0 THEN '+'
                               WHEN new.SALARY_A - old.SALARY_A < 0 THEN ''
                               ELSE ''
                           END,
                           new.SALARY_A - old.SALARY_A, ')') 
           END) AS SALARY_A_CHANGE
FROM table_new AS new 
LEFT JOIN table_old AS old ON new.UNIQUE_PJT = old.UNIQUE_PJT;
    Login or Signup to reply.
  2. 0 
    SELECT unique_pjt,
       old_data.project                      AS old_project,
       new_data.project                      AS new_project,
       old_data.job_title                    AS old_title,
       new_data.job_title                    AS new_title,
       old_data.salary_a                     AS old_salaryA,
       new_data.salary_a                     AS new_salaryA,
       COALESCE(new_data.salary_a) 
               - COALESCE(old_data.salary_a) AS change_salaryA,
       old_data.salary_b                     AS old_salaryB,
       new_data.salary_b                     AS new_salaryB,
       COALESCE(new_data.salary_b) 
               - COALESCE(old_data.salary_b) AS change_salaryB

-- this subquery collects all the unique index values,
-- regardless of whether they have been added or deleted
FROM ( SELECT unique_pjt FROM old_data  
       UNION DISTINCT                   
       SELECT unique_pjt FROM new_data   
       ) all_pjt

-- then join the data to this complete list
LEFT JOIN old_data USING (unique_pjt)
LEFT JOIN new_data USING (unique_pjt)

    This query produces complete comparing. You may remove some columns if you do not need in them. Also you may add WHERE which compares old and new values and and removes those rows which have no difference in the data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search