Mysql – thesql query matching multiple elements from a single table

MySQL

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.

Best Answer

  • You can't have anything about the inner tables in the WHERE clause for the outer joins to work as expected - move those to JOIN conditions
  • You would need FULL JOIN which is something that MySQL sadly doesn't support - you can emulate FULL JOIN with two outer joins and UNION

 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 OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND b.`cust` = 200
WHERE 1=1 AND a.`cust` = 100
UNION
 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
RIGHT OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND a.`cust` = 100
WHERE 1=1
AND b.`cust` = 200;