SQL One to Many with possible UNION
<p>I have a question about a query I am constructing and I would like to do it all in an SQL statement.</p>

<p>So, I have one table of data like this:</p>

<pre><code>+-----+-----+-----+-----+
| pid | did | src | val |
+-----+-----+-----+-----+
|   1 |   1 | lab |   0 |
|   1 |   1 | lab |   1 |
|   2 |   3 | pl  |   1 |
|   1 |   1 | pl  |   1 |
|   2 |   1 | med |   1 |
|   1 |   1 | med |   1 |
+-----+-----+-----+-----+
</code></pre>

<p>There is an ID column (pid), and a disease column (did), and a source (src) column and a value (val) column.  The disease column has also a normalized table I created like this:</p>

<pre><code>+-----+-----------------+
| did |      name       |
+-----+-----------------+
|   1 | Lung Cancer     |
|   2 | Alcoholism      |
|   3 | Obesity         |
|   4 | Diabetes        |
|   5 | Viral Infection |
+-----+-----------------+ 
</code></pre>

<p>The real trick here is that I need to do a query that results in a table that produces a new table that shows each pid (ID), and the associated text name for the disease (did) which is a probably a JOIN with the normalized table.</p>

<p>The tricky part is that for each ID, I need to count the occurrences of EACH of the (3) possible sources (src), so there will be maybe 5-10 occurences of an ID of 1, and each one of these will have a src associated: either MED, LAB, or PL.  I need to count these up per record and display in a table like this below.</p>

<pre><code>+-----+------------------------+--------------------------+----------------------+
| pid |      Lung Cancer       |        Alcoholism        |       Obesity              
+-----+------------------------+--------------------------+----------------------+
|   1 | Yes                    | Maybe                    | No                   |
|     | (med: 2, lab: 1, pl:1) | (med: 0, lab: 1, pl:1)   | (med:0, lab:0, pl:0) |
+-----+------------------------+--------------------------+----------------------+
</code></pre>

<p>So there may be 5-10 rows with and ID of 1, and for each row, there will be a src (source) assigned which will be one of three values (lab, pl, med) and these will have a value of 0 zero or 1 one.   </p>

<p>In the end there will be a count, and basically three states are arrived at (Yes) they have a disease, (All 1's or above for each source), or zero's for two, but one has 1 or more (maybe), or all zeros, (no)</p>

<p>I am not sure all of this can be accomplished with a SQL statement... or I can use JOIN/UNION or some combination to build a new table and place all the values, and then json_encode it (I'm using PHP), and then use Angular to sort through the src values and do the computations and show a bootstrap button for "Yes", "No" or "Maybe"</p>

<p>Thanks for you guys help!  This is quite a puzzling SQL statement, or at least I think so.</p>