I have two tables with an ID column (that matches the tables together) and concatenated columns where the concatenated values are in an arbitrary order. I want to compare to see if the two columns contain the exact same items (in any order) and output the ID if they don't.
select t1.personid, t1.products as t1products, t2.products as t2products
from table1 t1 (nolock)
inner join table2 t2 (nolock) on t1.personid = t2.personid
where t1.products != t2.products
If you have it in the form of one product for each row then you can query for all the results that don't have a match for both product and personid in the opposite table. Then do the same for the other table and union the results:
SELECT t1.personid, t1.product, '2' AS [Not Found In Table] FROM table1 t1 (nolock) LEFT JOIN table2 t2 ON t1.personid = t2.personid AND t1.product = t2.product WHERE t2.product IS NULL UNION SELECT t2.personid, t2.product, '1' AS [Not Found In Table] FROM table2 t2 (nolock) LEFT JOIN table1 t1 ON t2.personid = t1.personid AND t2.product = t1.product WHERE t1.product IS NULL
You could wrap this in a select and
CONCAT the results to feed you a nice list of what is missing from which table for each person who doesn't match.