MySQL - Finding mismatches between sets of "batched" data

Question

I have a table of customer order data that looks like this:

enter image description here

As you can see, there's a row for billing and a row for shipping for EACH order. parent_id is what tethers these rows together in to one order. What I need to do is write a query that finds all cases where there's a last_name OR zip_code mismatch between two rows with the same parent_id (i.e. the billing and shipping info is different). I'm at something of a loss for how to construct this query because this doesn't work for pretty obvious reasons:

SELECT * 
FROM order_addresses 
WHERE parent_id = parent_id
AND last_name <> last_name

What I think I need to really do is find all cases where parent_id matches FIRST and then step down and see whether zip_code and last_name also match and display all results where they do not. I don't know how to do that. Any pointers would be great, thank you!

I don't like the way that table is designed. Ideally you would have columns each for shipping and billing information rather than a column that tells you which it is. — Phrancis, May 23 '14 at 19:35
I agree, but I didn't design the table. This table exists (along with many tables like it) thanks to Magento, and I simply have to live with it. :D — Kale, May 23 '14 at 20:54

Linger · Accepted Answer · 2014-05-23T19:39:16.167

3

How about the following:

SELECT * 
FROM order_addresses o1
  INNER JOIN order_addresses o2 
  ON o1.parent_id = o2.parent_id 
  AND o1.entity_id <> o2.entity_id
WHERE o1.last_name <> o2.last_name
  OR o1.zip_code <> o2.zip_code

edited May 23 '14 at 19:39

answered May 23 '14 at 19:33

Linger

14,942
23
52
79

1

You should add the zip code logic to fully answer the question. – Gordon Linoff May 23 '14 at 19:37
1

and 1. correct the typo in "parrent_id" 2. select fields from either o1 or o2, i.e. SELECT o1.* instead of SELECT * to avoid duplicate records – Haleemur Ali May 23 '14 at 19:40
**@Gordon** and **Haleemur Ali**, thanks for the observations. I missed the zip code requirement. I also fixed the `parent_id` spelling. – Linger May 23 '14 at 19:41
Brilliant! Thank you. I didn't realize you were able to specify table "instances"... – Kale May 23 '14 at 20:42
It's called a self-join - More info here: http://stackoverflow.com/questions/1284441/how-does-a-mysql-self-join-work – Phrancis May 23 '14 at 20:59
Thanks for the info Phrancis - can you join other external tables in to the query if you're using self-joins? That's the next step... – Kale May 24 '14 at 00:20
yes you can join as many tables as you want and the same table several times. – Linger May 24 '14 at 11:58

score 0 · Answer 2 · edited May 23 '17 at 10:34

This is my take on this. Not 100% certain it will work. You might be able to pivot it too if you want to look into that. This is really not good table design.

DROP PROCEDURE IF EXISTS sp_BillShipCompare;
CREATE PROCEDURE sp_BillShipCompare();

DELIMITER //

SET @start = (SELECT MIN(parent_id FROM order_addresses);
SET @stop = (SELECT MAX(parent_id FROM order_addresses);

WHILE @start <= @stop DO
BEGIN

SELECT 
    oab.zip_code AS 'billing_zip',
    oas.zip_code AS 'shipping_zip',
    oab.name AS 'billing_name',
    oas.name AS 'shipping_name'
FROM order_addresses AS oab
INNER JOIN order_addresses AS oas
ON oab.parent_id = oas.parent_id
WHERE oab.parent_id = @start
OR oas.parent_id = @start
AND oab.address_type = 'billing'
AND oas.address_type = 'shipping'
AND oab.zip_code <> oas.zip_code
OR oab.name <> oas.name;
SET @start = @start+1;
END;
END WHILE//

DELIMITER ;

Yeah, the table design sucks. Thanks Magento. ;) – Kale May 23 '14 at 20:43 — Kale, May 23 '14 at 20:43

MySQL - Finding mismatches between sets of "batched" data

2 Answers2