mysql query matching multiple elements from a single table 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("&lt;&lt; ",a.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("&lt;&lt; ",a.`data2`," &gt;&gt;")) 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("&lt;&lt; ",b.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("&lt;&lt; ",b.`data2`," &gt;&gt;")) 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    | &lt;&lt; 789 &gt;&gt; | SevenEightNine | no match | 200  | 1    | &lt;&lt; 7890 &gt;&gt;  | 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    | &lt;&lt; 789 &gt;&gt; | &lt;&lt; SevenEightNine &gt;&gt; | no match | NULL | NULL | NULL        | NULL
NULL | NULL | NULL      | NULL                 | no match | 200  | 1    | &lt;&lt; 7890 &gt;&gt;  | &lt;&lt; SevenEightNineZero &gt;&gt;


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.
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("&lt;&lt; ",a.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("&lt;&lt; ",a.`data2`," &gt;&gt;")) 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("&lt;&lt; ",b.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("&lt;&lt; ",b.`data2`," &gt;&gt;")) 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("&lt;&lt; ",a.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("&lt;&lt; ",a.`data2`," &gt;&gt;")) 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("&lt;&lt; ",b.`data1`," &gt;&gt;")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("&lt;&lt; ",b.`data2`," &gt;&gt;")) 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;