Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>For your example, I think this query gets us the resultset we need:</p> <pre><code>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 </code></pre> <p>This should give this resultset:</p> <pre><code>+-------+-------+ | match | map | +-------+-------+ | 20 | 2 | | 2A | 2 | | 2B | 2 | | 3 | 3 | | 4 | 4 | | 2 | 2 | +-------+-------+ </code></pre> <p>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.</p> <p>(There are other ways to write the query, e.g. use a CASE expression, rather than an IF, or more concisely:</p> <pre><code>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 </code></pre> <p>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.)</p> <pre><code>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 </code></pre> <hr> <p><strong>Followup</strong></p> <p><strong>Q:</strong> What if a CODE has both PARENT_CODE and OLD_CODE (I just found out that this is a possible scenario,...</p> <p><strong>A:</strong> You'd need to test the query to returns the "match" and "map" code.</p> <p>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.</p> <p>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.</p> <p>I'm going to assume that the CODE column is NOT NULL in the <code>tbl_item</code> table (just so to simplify this.)</p> <p>These are the four queries I'm thinking of, to handle all those cases:</p> <pre><code>-- 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 </code></pre> <p>These would be combined together, using UNION ALL operators.</p> <p>I can envision wacky data, where the same CODE appears more than once, and each might point to a different PARENT_CODE </p> <p>Some example wacky (unexpected) rows in TBL_ITEM.</p> <pre><code>+------+-------------+----------+ | CODE | PARENT_CODE | OLD_CODE | +------+-------------+----------+ | 77A | 77A | NULL | | 77A | 77B | 77A | | 77 | 77 | 77A | | 77 | 77A | NULL | +------+-------------+----------+ </code></pre> <p>What would we do with a mess like this?</p> <p>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...</p> <p>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:</p> <pre><code>SELECT u.match , MIN(u.map) AS map FROM ( -- query to get match/map rowset here ) u GROUP BY u.match </code></pre> <p>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 <code>m</code>.</p> <pre><code>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 </code></pre> <p>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 (<em>unique</em> and <em>not null</em>) <code>match_code</code> and an optional <code>map_code</code>.</p> <p>This would make for a very simple query:</p> <pre><code>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) </code></pre> <p>It's generating the "tbl_match_map" rowset from the given table that is the hard work.</p>
    singulars
    1. This table or related slice is empty.
    1. POMultiple column reference with a parent code
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USspencer7593
    1. USspencer7593
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMultiple column reference with a parent code
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI had some difficulty deriving the query that gets the two columns, but I think what I have now works.
      singulars
      1. PO
      1. USspencer7593
    2. COThank you @spencer7593, a lot. What if a CODE has both PARENT_CODE and OLD_CODE (I just found out that this is a possible scenario, hence the late response)? Should that be another question entirely? Or can the answer above be easily tweaked to fit this development?
      singulars
      1. PO
      1. USesandrkwn
    3. COAlthough I doubt it will involve just some minor tweaking since you did mention that "match" has to be unique
      singulars
      1. PO
      1. USesandrkwn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload