In a MySQL table there are the following columns:
id, spn(PK), cat1, cat2, cat3, act1, act2, act3
(cat fields are of VARCHAR type, act fields are INT) and I need to perform a check to see if the total of unique combinations of all catΧ fields is equal to the unique combinations of all actΧ fields and if not, return the list of spn values for the found mismatches.
My initial thought was to perform a selection of concat(cat1,cat2,cat3) as A as well as the concat(act1,act2,act3) as B and compare them but I'm not quite sure how to perform the comparison and return the list of found spn's where A <> B. Any ideas are welcome.
My app is in PHP so if you can suggest a solution using also PHP that's fine.
NOTE: Based on the first 2 answers, I'd like to clarify that i'm trying to check if the number of unique combinations of all cat fields is equal to that of the combination of act fields.
I don't know column types, so I will go with the most standard solution:
SELECT spn
FROM table
WHERE cat1 != act1 || cat2 != act2 || cat3 != act3
Maybe your database design could be improved. Introducing number in column names is often synonym of bad design.
EDIT: Assuming order is not important in your permutation:
SELECT id
FROM files_imported
WHERE ((cat1 != cat2) + (cat1 != cat3) + (cat2 != cat3)) != ((act1 != act2) + (act1 != act3) + (act2 != act3))