StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL How do I create this subquery?
text
Body
copied!<p><strong>I have the following tables</strong></p> <p>Table Farm</p> <pre><code>+---------+--------+-------------------+-----------+------------+ | FARM_ID |Stock_ID| FARM_TITLE | Size | FARM_VALUE | +---------+--------+-------------------+-----------+------------+ | 2 | 1 | AgriZone | M | 202 | | 3 | 1 | Cow Mill | L | 11 | | 4 | 2 | Beef Farm | H | 540 | | 5 | 2 | CattleOne | M | 1080 | | 6 | 2 | FarmOne | L | 455 | | 7 | 3 | Perdue | H | 333 | | 8 | 4 | Holstein | M | 825 | | 10 | 1 | Dotterers | H | 98 | +---------+--------+-------------------+-----------+------------+ </code></pre> <p>Table Gate</p> <pre><code>+---------+---------+------------+ | GATE_ID | FARM_ID | FARM_VALUE | +---------+---------+------------+ | 1 | 2 | 0 | | 1 | 3 | 0 | | 1 | 4 | 540 | | 2 | 4 | 550 | | 3 | 4 | 560 | | 4 | 4 | 570 | | 5 | 4 | 580 | | 6 | 4 | 590 | | 1 | 5 | 1080 | | 2 | 5 | 1100 | | 3 | 5 | 1120 | | 4 | 5 | 1140 | | 5 | 5 | 1160 | | 6 | 5 | 1180 | | 1 | 6 | 455 | | 2 | 6 | 536 | | 3 | 6 | 617 | | 4 | 6 | 698 | | 5 | 6 | 779 | | 6 | 6 | 860 | | 1 | 7 | 0 | | 1 | 8 | 0 | | 1 | 10 | 0 | +---------+---------+------------+ </code></pre> <p>Table Origin</p> <pre><code>+--------+----------+ | ORI_ID | ORI_NAME | +--------+----------+ | 1 | US | | 2 | CA | | 3 | MX | +--------+----------+ </code></pre> <p>Table Stock</p> <pre><code>+--------+--------+-------------------+ |Stock_ID| ORI_ID | Stock_TITLE | +--------+--------+-------------------+ | 1 | 1 | P1 | | 2 | 2 | P3 | | 3 | 3 | Q4 | | 4 | 3 | B3 | +--------+--------+-------------------+ </code></pre> <p>Table Results</p> <pre><code>+-----------+---------+---------+------------+------------+ | RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% | +-----------+---------+---------+------------+------------+ | 1 | 7 | 1 | 333 | 100 | | 2 | 8 | 1 | 825 | 100 | | 3 | 6 | 1 | 455 | 40 | | 4 | 6 | 2 | 536 | 0 | | 5 | 6 | 3 | 617 | 0 | | 6 | 6 | 4 | 698 | 100 | | 7 | 6 | 5 | 779 | 0 | | 8 | 6 | 6 | 860 | 10 | | 9 | 4 | 1 | 540 | 100 | | 10 | 4 | 2 | 550 | 90 | | 11 | 4 | 3 | 560 | 0 | | 12 | 4 | 4 | 570 | 100 | | 13 | 4 | 5 | 580 | 10 | | 14 | 4 | 6 | 590 | 0 | | 15 | 5 | 1 | 1080 | 0 | | 16 | 5 | 2 | 1100 | 0 | | 17 | 5 | 3 | 1120 | 0 | | 18 | 5 | 4 | 1140 | 50 | | 19 | 5 | 5 | 1160 | 0 | | 20 | 5 | 6 | 1180 | 100 | | 21 | 3 | 1 | 11 | 100 | | 22 | 10 | 1 | 98 | 90 | | 23 | 2 | 1 | 202 | 100 | +-----------+---------+---------+------------+------------+ </code></pre> <p><strong>Annotated Result table: Same as above ^</strong></p> <pre><code>+-----------+---------+---------+------------+------------+ | RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% | +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 1 | 7 | 1 | 333 | 100 | <--|H-Case {H} +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 2 | 8 | 1 | 825 | 100 | <--|M-Case {M} +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 3 | 6 | 1 | 455 | 40 | | 4 | 6 | 2 | 536 | 0 | | 5 | 6 | 3 | 617 | 0 | | 6 | 6 | 4 | 698 | 100 | <--|L | 7 | 6 | 5 | 779 | 0 | | | 8 | 6 | 6 | 860 | 10 | | +-----------+---------+---------+------------+------------+ | | 9 | 4 | 1 | 540 | 100 | | | 10 | 4 | 2 | 550 | 90 | | | 11 | 4 | 3 | 560 | 0 | | | 12 | 4 | 4 | 570 | 100 | <--+M-case {H,M,L} | 13 | 4 | 5 | 580 | 10 | | | 14 | 4 | 6 | 590 | 0 | | +-----------+---------+---------+------------+------------+ | | 15 | 5 | 1 | 1080 | 0 | | | 16 | 5 | 2 | 1100 | 0 | | | 17 | 5 | 3 | 1120 | 0 | | | 18 | 5 | 4 | 1140 | 50 | <--|H | 19 | 5 | 5 | 1160 | 0 | | 20 | 5 | 6 | 1180 | 100 | +-----------+---------+---------+------------+------------+ +-----------+---------+---------+------------+------------+ | 21 | 3 | 1 | 11 | 100 | <--|L | 22 | 10 | 1 | 98 | 90 | <--+H-case {H,M,L} | 23 | 2 | 1 | 202 | 100 | <--|M +-----------+---------+---------+------------+------------+ </code></pre> <p><strong>Computations required:</strong></p> <ul> <li>Type can have only three values at most: {H, M, L};</li> <li>When all values are present, they are graded as followed: H=70 M=20 L=10</li> <li><p>ALL unique casese are</p></li> <li><p>Case {H,M} : H=80 M=20</p></li> <li>Case {M,L} : M=60 L=40</li> <li>Case {H,L} : H=90 L=10</li> <li>Case {H} : H=100</li> <li>Case {M} : M=100</li> <li>Case {L} : L=100</li> <li>Case {H,M,L} : H=70 M=20 L=10</li> </ul> <p><strong>Further Explanation</strong></p> <ul> <li><strong>Only</strong> Stock with with <strong>atleast one</strong> <code>GATE</code>, fully satisfied can get 100 points max <ol> <li>Example: <code>Q4</code> has 3 sets of 6 <code>GATES</code>; Only one <code>GATE</code> set has to be satsified (have a score present).</li> <li>The points present must be Multiplied against the particular case it pertains Example: <code>Q4</code> has case {H,M,L} which means H=70; M=20; L=10 This would results in (70*100%)+(20*50%)+(10*100%)=90 <strong>(look back above at the Result table annotations)</strong></li> 2. </ol></li> <li>Points should still be considered and accounted for even when a gate hasn't been fully satisfied. The gate with MAX points earned should be kept when no gates have been fully satisfied. (Will provide further explanation if not understood)</li> </ul> <p><strong>If we Perform a query to make sense of the tables and data it will look like below</strong></p> <pre><code>+---------+-----------+---------------+-----------+---------+-----------+---------+ | Origin | Stock | Farm Title | Farm Value| Gate | Size | Score | +---------+-----------+---------------+-----------+---------+-----------+---------+ | US | P1 | Perdue | 333 | 1 | H | 100 | | US | P3 | Holstein | 825 | 1 | M | 100 | | CA | Q4 | FarmOne | 455 | 1 | L | 40 | | CA | Q4 | FarmOne | 536 | 2 | L | 0 | | CA | Q4 | FarmOne | 617 | 3 | L | 0 | | CA | Q4 | FarmOne | 698 | 4 | L | 100 | | CA | Q4 | FarmOne | 779 | 5 | L | 0 | | CA | Q4 | FarmOne | 860 | 6 | L | 10 | | CA | Q4 | Beef Farm | 540 | 1 | H | 0 | | CA | Q4 | Beef Farm | 550 | 2 | H | 90 | | CA | Q4 | Beef Farm | 560 | 3 | H | 0 | | CA | Q4 | Beef Farm | 570 | 4 | H | 100 | | CA | Q4 | Beef Farm | 580 | 5 | H | 10 | | CA | Q4 | Beef Farm | 590 | 6 | H | 0 | | CA | Q4 | CattleOne | 1080 | 1 | M | 0 | | CA | Q4 | CattleOne | 1100 | 2 | M | 0 | | CA | Q4 | CattleOne | 1120 | 3 | M | 0 | | CA | Q4 | CattleOne | 1140 | 4 | M | 50 | | CA | Q4 | CattleOne | 1160 | 5 | M | 100 | | CA | Q4 | CattleOne | 1180 | 6 | M | 0 | | MX | B3 | Cow Mill | 11 | 1 | L | 100 | | MX | B3 | Dotterers | 98 | 1 | H | 90 | | MX | B3 | AgriZone | 202 | 1 | M | 100 | +---------+-----------+---------------+-----------+---------+-----------+---------+ </code></pre> <p><strong>Desire Results</strong></p> <pre><code>+---------+-------------------+-------+ | Origin | Stock | score | +---------+-------------------+-------+ | US | P1 | 100 | | US | P3 | 100 | | CA | Q4 | 90 | | MX | B3 | 93 | +---------+-------------------+-------+ </code></pre> <p><img src="https://i.stack.imgur.com/rgIGK.jpg" alt="Explanation"></p> <p>Since <code>origin</code> has a <code>stock</code> which consists of 3 different <code>farms</code> and those <code>farms</code> have 6 <code>gates</code> each. As long as one <code>gate</code>-set (numerically matching <code>gates</code>) is scored to SOME value we can consider the entire <code>STOCK</code> found completely. This is the only way a <code>stock</code> can be considered 100. </p> <p>Moreover and to reiterate, <code>STOCK</code> Q4 has case: {H,M,L} and all of <code>gate (4)</code> was found to some degree. <code>gate</code> 4 has the score (100% * H) + (50% * M) + (100% * L) which equals (70*100%) + (20*50%) + (10*100%) = 90</p> <p>Hence: (Taken from above)</p> <pre><code> | CA | Q4 | 90 | </code></pre> <p><strong>QED</strong></p> <p><strong>So what I need help with</strong> is creating the subquery/subselect to do make this computation work. I set up everything in the scenario above (along with a query in progress that I've been working with) in the SQL fiddle link below. </p> <p>Thanks greatly stackoverflow community.</p> <p><a href="http://sqlfiddle.com/#!2/4be72/3" rel="noreferrer"><strong>> The above problem in SqlFiddle can be found here <</strong></a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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