StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInner join on a distinct set of parameters
text
Body
copied!<p>I've tried a few of the similar SO questions, but I can't seem to figure it out.</p> <p>On the first inner join, I only want to bring in DISTINCT <code>function</code> columns code and serial_id. So when I do my SUM selects, it calculates one per distinct. Ie there are multiple rows with the same func.code and func.serial_id. I only want 1 of them.</p> <pre><code>SELECT sl.imp_id, lat.version, SUM(IF(lat.status = 'P',1,0)) AS powered, SUM(IF(lat.status = 'F',1,0)) AS functional FROM slots sl INNER JOIN functions func ON sl.id = func.slot_id INNER JOIN latest_status lat ON lat.code = func.code AND lat.serial_id = func.serial_id WHERE sl.id=55 GROUP BY sl.imp_id, lat.version </code></pre> <p>EDIT 2 - sample data explanation -------------------</p> <pre><code>slots - id, imp_id, name functions - id, slot_id, code, serial_id latest_status - id, code, serial_id, version, status **slots** id imp_id name 1 5 'the name' 2 5 'another name' 3 5 'name!' 4 5 'name!!' 5 5 'name!!!' 6 5 'testing' 7 5 'hi' 8 5 'test' **functions** id slot_id code serial_id 1 1 11HRK 10 2 2 22RMJ 11 3 3 26OLL 01 4 4 22RMJ 00 6 6 11HRK 10 7 7 11HRK 10 8 8 22RMJ 00 **latest_status** id code serial_id version status 1 11HRK 10 1 F 1 11HRK 10 2 P 3 22RMJ 11 1 P 4 22RMJ 11 2 F 5 26OLL 01 1 F 6 26OLL 01 2 P 7 22RMJ 00 1 F 8 22RMJ 00 2 F </code></pre> <p>After running the query, the result should look like this:</p> <pre><code>imp_id version powered functional 5 1 1 3 5 2 2 2 </code></pre> <p>The function table gets rolled up based on the code, serial_id. 1 row per code, serial_id. It then gets joined onto the latest_status table based on the serial_id and code, which is a one (functions) to many (latest_status) relationship, so two rows come out of this, one for each version. </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