StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCombine multiple queries against one table into multiple fields of one result set
text
Body
copied!<p><strong>ANSWER</strong>: Here's the final query built from @Michael 's answer:</p> <pre><code>SELECT Emp_ID, MAX(CASE WHEN Skill_ID = 'WLN' THEN (Rating > 0) END) AS `WLN`, MAX(CASE WHEN Skill_ID = 'LOC' THEN (Rating > 0) END) AS `LOC`, MAX(CASE WHEN Skill_ID = 'BRV' THEN (Rating > 0) END) AS `BRV`, AVG(CASE WHEN Skill_ID IN ('KWH','SIC','DOL') THEN (Rating) END) > 0 AS `KSD` FROM Emp_Skill WHERE Emp_ID IN (120,348,361,370) GROUP BY Emp_ID </code></pre> <hr> <p><strong>QUESTION</strong>:</p> <p>I have the following <code>Emp_Skill</code> table:</p> <pre><code>Skill_ID* Emp_ID* Rating -------------------------- WLN 120 6 WLN 348 5 WLN 361 7 WLN 370 8 LOC 120 7 LOC 370 7 LOC 348 7 BRV 120 3 LOC 361 6 BRV 348 1 KWH 348 5 KWH 120 5 KWH 361 5 KWH 370 5 SIC 361 8 SIC 348 4 SIC 120 2 DOL 348 5 DOL 361 8 </code></pre> <p>and I need to know <strong>if an arbitrary number of employees have a positive skill rating for each skill</strong>.</p> <p>So let's say I want to look at this info for employees <em>120</em>, <em>348</em>, <em>361</em> and <em>370</em>.</p> <p><em>Query 1</em> for skill <code>WLN</code> is:</p> <pre><code>SELECT `Emp_ID`, `Rating` > 0 AS `WLN` FROM `Emp_Skill` WHERE `Skill_ID` = 'WLN' AND `Emp_ID` IN (120,348,361,370) </code></pre> <p>which returns:</p> <pre><code>Emp_ID WLN ------------ 120 1 348 1 361 1 370 1 </code></pre> <p><em>Query 2</em> for skill <code>LOC</code> is:</p> <pre><code>SELECT `Emp_ID`, `Rating` > 0 AS `LOC` FROM `Emp_Skill` WHERE `Skill_ID` = 'LOC' AND `Emp_ID` IN (120,348,361,370) </code></pre> <p>which returns:</p> <pre><code>Emp_ID LOC ------------ 120 1 348 1 361 1 370 1 </code></pre> <p><em>Query 3</em> for skill <code>BRV</code> is:</p> <pre><code>SELECT `Emp_ID`, `Rating` > 0 AS `BRV` FROM `Emp_Skill` WHERE `Skill_ID` = 'BRV' AND `Emp_ID` IN (120,348,361,370) </code></pre> <p>which returns:</p> <pre><code>Emp_ID BRV ------------ 120 1 348 1 </code></pre> <p>and <em>Query 4</em> for skill <code>KSD</code> is:</p> <pre><code>SELECT `Emp_ID`, AVG(`Rating`) > 0 AS `KSD` FROM `Emp_Skill` WHERE `Skill_ID` IN ('KWH','SIC','DOL') AND `Emp_ID` IN (120,348,361,370) GROUP BY `Emp_ID` </code></pre> <p>which returns:</p> <pre><code>Emp_ID KSD ------------ 120 1 348 1 361 1 370 1 </code></pre> <p><em>Question</em> is: <strong>How do I efficiently combine these queries into one result table and have them all in one go</strong>, i.e.:</p> <pre><code>Emp_ID WLN LOC BRV KSD ------------------------------ 120 1 1 1 1 348 1 1 1 1 361 1 1 NULL? 1 370 1 1 NULL? 1 </code></pre> <p>possibly from a combined <code>SELECT</code> statement? (the <em><code>NULL</code></em> value could be anything that evaluates to <code>FALSE</code> in the returned result set)</p> <p><strong>OR</strong>: performance-wise, will it be better if I just query each skill rating one by one (using the 4 queries above)?</p> <p>Sorry if this is elementary for some peeps out there, but I've pulled a lot of hair out and I still haven't killed this. lol. Thanks in advance.</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