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_descriptionFROM (SELECT temp_category_number AS category_number, temp_category_description AS category_description FROM temp_categoriesUNION ALLSELECT data_category_number AS category_number, data_category_description AS category_description FROM data_categories) tblGROUP BY category_number, category_descriptionHAVING count(*) = 1ORDER BY category_number;
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?