0

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.

1
  • 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;
  • you are a star. I spent so long, and tried so many variations, that I think I simply got myself completely lost. It works perfectly. Cheers, – IGGt Apr 15 '15 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.