Avatar of brstores
brstores

asked on 

MySQL, compare columns from 2 tables and show difference and source tables

I'm trying to compare two tables.  The "data_categories" table contains a column that identifies a category of items by a number, the description of that category and a whole lot of attributes that someone has manually assigned to those individual categories.  Once a month, I'm going to get a current product category list that I want use to compare to the existing records in the database to see if any categories have been added, changed or deleted.  This list will only contain the category number and description.  My thought was that I would import that monthly list into a temporary table and then run some SQL to find the differences.  Here is the query that I came up with:

SELECT category_number, category_description
FROM (
SELECT temp_category_number AS category_number, temp_category_description AS category_description FROM temp_categories
UNION ALL
SELECT data_category_number AS category_number, data_category_description AS category_description FROM data_categories
) tbl
GROUP BY category_number, category_description
HAVING count(*) = 1
ORDER BY category_number;

Open in new window


That query does show me what records in the two tables have been changed.  However, without knowing the source table of the returned record I'm unable to tell if a category has been added or deleted from the program that creates the monthly list.  How would I add another piece of data to my result that would identify the table source of the record?
MySQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon