I have this awkward query I'm trying to figure out. Basically it checks a table for matching / non-matching entries (data1, data2).
My Table is:
CREATE TABLE `test`.`tab1` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`cust` INT UNSIGNED NOT NULL,
`type` TINYINT NOT NULL, #form
`data1` INT UNSIGNED NOT NULL, #xml
`data2` VARCHAR(45) NOT NULL, #custXML
PRIMARY KEY (`id`));
Test Data:
INSERT INTO `test`.`tab1`
VALUES
(1,100,1,123,'OneTwoThree'),
(2,100,1,456,'FourFiveSix'),
(3,100,1,789,'SevenEightNine'),
(4,200,1,123,'OneTwoThree'),
(5,200,1,456,'FourFiveSix'),
(6,200,1,789,'SevenEightNine');
In the given example each type has two data elements data1 and data2.
I want to get the data elements for cust=100 AND type=1 and compare them to cust=200 AND type=1
So with the above Test Data I would end up with:
Desired Result:
cust | type | data1 | data2 | match | cust | type | data1 | data2
100 | 1 | 123 | OneTwoThree | match | 200 | 1 | 123 | OneTwoThree
100 | 1 | 456 | FourFiveSix | match | 200 | 1 | 456 | FourFiveSix
100 | 1 | 789 | SevenEightNine | match | 200 | 1 | 789 | SevenEightNine
This I can do using a self join:
SELECT
a.`cust`
,a.`type`
,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
,a.`data2`,
IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
,b.`cust`
,b.`type`
,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
LEFT JOIN `test`.`tab1` b ON (a.`type`=b.`type`)
WHERE 1=1
AND a.`cust` = 100 AND b.`cust` = 200
AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`);
If data1 OR data2 changes that's fine, as the above query handles that.
UPDATE `test`.`tab1` SET `data1` = 7890 WHERE id = 6;
cust | type | data1 | data2 | match | cust | type | data1 | data2
100 | 1 | 123 | OneTwoThree | match | 200 | 1 | 123 | OneTwoThree
100 | 1 | 456 | FourFiveSix | match | 200 | 1 | 456 | FourFiveSix
100 | 1 | << 789 >> | SevenEightNine | no match | 200 | 1 | << 7890 >> | SevenEightNineZero
The problem I have is if both data1 and data2 change then my comparison falls apart.
e.g.
UPDATE `test`.`tab1` SET `data1` = 7890, `data2` = 'SevenEightNineZero' WHERE id = 6;
I am trying to get a result similar to:
cust | type | data1 | data2 | match | cust | type | data1 | data2
100 | 1 | 123 | OneTwoThree | match | 200 | 1 | 123 | OneTwoThree
100 | 1 | 456 | FourFiveSix | match | 200 | 1 | 456 | FourFiveSix
100 | 1 | << 789 >> | << SevenEightNine >> | no match | NULL | NULL | NULL | NULL
NULL | NULL | NULL | NULL | no match | 200 | 1 | << 7890 >> | << SevenEightNineZero >>
I figure I could put it into a MySQL Stored Procedure, and use a cursor to loop through the data and do the comparison. Or I could simply break it up and put each customers data into temporary tables, and run each comparison as a separate query to build another temp table with the result.
But if there's a way to do it in a single Query it would be far better.
