Multiple column reference with a parent code

Question

I'm wondering how I could reference the three columns from TBL_ITEM to get SUM(QTY) FROM TBL_INVTY.

TBL_ITEM

+------+-------------+----------+
| CODE | PARENT_CODE | OLD_CODE |
+------+-------------+----------+
|    2 |        NULL |       20 |
|   2A |           2 |     NULL |
|   2B |           2 |     NULL | 
|    3 |        NULL |     NULL |
|    4 |        NULL |     NULL |
|   20 |        NULL |     NULL |
+------+-------------+----------+

TBL_INVTY

+------+-----+
| CODE | QTY |
+------+-----+
|    2 |   2 |
|   2A |   4 |
|   2B |   1 |
|    3 |   3 |
|    4 |   5 |
|   20 |   2 |
+------+-----+

And, arrive on this:

+------+-----+
| CODE | QTY |
+------+-----+
|    2 |   9 |
|    3 |   3 |
|    4 |   5 |
+------+-----+

The first row on the last table WHERE CODE=2 has a QTY of 9 because I took the SUM(QTY) from TBL_INVTY WHERE CODE IN (20, 2A, 2B, 2) with respect to the references made in TBL_ITEM.

Answer 1

The trick would be to get a two column resultset from tbl_item. The two columns I would call "match" and "map". The values in the "match" column are what we are going to use in the JOIN predicate, to find "matching" rows. But we're going to return the value from the map column, and do the GROUP BY on that.

For your example, I think this query gets us the resultset we need:

SELECT IF(i.old_code IS NOT NULL,i.old_code,i.code) AS `match`
     , IF(i.parent_code IS NOT NULL,i.parent_code,i.code) AS map
  FROM tbl_item i
  LEFT
  JOIN tbl_item j
    ON j.old_code = i.code
 WHERE j.old_code IS NULL
 UNION ALL
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.old_code IS NOT NULL

This should give this resultset:

+-------+-------+
| match | map   |
+-------+-------+
|   20  |   2   |
|   2A  |   2   |
|   2B  |   2   |
|    3  |   3   |
|    4  |   4   |
|    2  |   2   |
+-------+-------+

We need to ensure that the "match" column is unique, so that we don't inadvertently match a row from tbl_invty to more than one row from this set. That's not a problem in this set of data, but in the more general case it could be. Also, this only does "one level" of old to new. If a "new" code is later superseded, then this query won't find the "newest", only the value from the row. Again, not a problem with this data, but a more general case this might be an issue.

(There are other ways to write the query, e.g. use a CASE expression, rather than an IF, or more concisely:

SELECT IFNULL(i.old_code,i.code) AS `match`
     , IFNULL(i.parent_code,i.code) AS map
  FROM tbl_item i 
  LEFT
  JOIN tbl_item j
    ON j.old_code = i.code
 WHERE j.old_code IS NULL
 UNION ALL
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.old_code IS NOT NULL      

Anyway, once we have a query that gets us a suitable resultset, getting the SUM(QTY) us easy. We just use that query as a rowsource in another query. (We call this an "inline view", although MySQL calls it a "derived table", which more accurately describes how MySQL actually processes the view query.)

SELECT m.map      AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN (
         SELECT IFNULL(i.old_code,i.code) AS `match` 
              , IFNULL(i.parent_code,i.code) AS map 
           FROM tbl_item i 
           LEFT 
           JOIN tbl_item j 
             ON j.old_code = i.code 
          WHERE j.old_code IS NULL 
          UNION ALL
         SELECT k.code
              , k.code
           FROM tbl_item k
          WHERE k.old_code IS NOT NULL
       ) m 
    ON m.match = v.code 
 GROUP 
    BY m.map 

---

Followup

Q: What if a CODE has both PARENT_CODE and OLD_CODE (I just found out that this is a possible scenario,...

A: You'd need to test the query to returns the "match" and "map" code.

Given that new case (a row with both a PARENT_CODE and an OLD_CODE), there is an adjustment needed. You'd really need to run through each case, and determine what should be returned for each case.

It seems like we want to pick up EVERY code in the table (whether it's in the CODE column, the PARENT_CODE column, or the OLD_CODE column) as the "match" code, and derive the appropriate "map" code for each one.

I'm going to assume that the CODE column is NOT NULL in the tbl_item table (just so to simplify this.)

These are the four queries I'm thinking of, to handle all those cases:

-- rows with PARENT_CODE,  match=CODE map=PARENT_CODE
SELECT i.code AS `match_code`
     , i.parent_code AS `map_code`
  FROM tbl_item i
 WHERE i.parent_code IS NOT NULL


-- rows with PARENT_CODE and OLD_CODE,  match=OLD_CODE map=PARENT_CODE
SELECT j.old_code
     , j.parent_code
  FROM tbl_item j
 WHERE j.parent_code IS NOT NULL
   AND j.old_code IS NOT NULL 

-- rows with no PARENT_CODE,  match=CODE map=CODE   
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.parent_code IS NULL

-- rows with OLD_CODE and no PARENT_CODE,  match=OLD_CODE map=CODE
SELECT l.old_code
     , l.code
  FROM tbl_item l
 WHERE l.parent_code IS NULL
   AND l.old_code IS NOT NULL

These would be combined together, using UNION ALL operators.

I can envision wacky data, where the same CODE appears more than once, and each might point to a different PARENT_CODE

Some example wacky (unexpected) rows in TBL_ITEM.

+------+-------------+----------+
| CODE | PARENT_CODE | OLD_CODE |
+------+-------------+----------+
|  77A |         77A |     NULL |
|  77A |         77B |      77A |
|   77 |          77 |      77A |
|   77 |         77A |     NULL |
+------+-------------+----------+

What would we do with a mess like this?

As far as getting a SUM(QTY), what we need to insure in the "map/match" rowset is that a given CODE appears only ONCE. (If we get multiples in there, then the SUM is going to be too high, because we are going to match to more than one...

The quick fix is to wrap the query in another query, to eliminate duplicates and pick just ONE code to map to. This may not be the "right" fix, but it gets us a resultset that we can work with:

SELECT u.match
     , MIN(u.map) AS map
  FROM (
          -- query to get match/map rowset here
       ) u
 GROUP BY u.match

We would then use this query as an inline view that gets the SUM(QTY), the same query as before. We've just changed that query in the inline view aliased as m.

SELECT m.map      AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN (
         SELECT u.match
              , MIN(u.map) AS map
           FROM (
                  -- query to get match/map rowset here
                ) u
          GROUP BY u.match
       ) m 
    ON m.match = v.code 
 GROUP
    BY m.map

At this point, it almost goes without saying, what we'd really like, to make the query simple, is a with just two columns, a (unique and not null) match_code and an optional map_code.

This would make for a very simple query:

SELECT IFNULL(m.map_code,m.match_code) AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN tbl_match_map m
    ON m.match_code = v.code
 GROUP
    BY IFNULL(m.map_code,m.match_code)

It's generating the "tbl_match_map" rowset from the given table that is the hard work.