Self Join With Multiple Conditions
<p>I'm using mysql to storing my chemical analysis. And filtering the results with html/php and to generate the query to fetch results I want. Now things getting complex for me and I'm trying to self join the table to apply all the filters.</p>

<p>My table desing is like that with more than 50k rows.</p>

<pre><code>+--------+---------+-------------+----------------+
| Column |  Type   |    Index    |                |
+--------+---------+-------------+----------------+
| RID    | int     | primary_key | auto increment |
| ID     | int     | index       |                |
| Type   | int     |             |                |
| Order  | int     |             |                |
| Num    | int     |             |                |
| Val1   | decimal |             |                |
| Val2   | decimal |             |                |
+--------+---------+-------------+----------------+
</code></pre>

<p>Every sample has an <strong>ID</strong>, different <strong>types</strong> and <strong>order</strong>. <strong>Val1</strong> and <strong>Val2</strong> are the results of <strong>Num</strong> type analyse. There 42 different Num by now.</p>

<p>For example If my filters are like that,</p>

<pre><code>Select Types (1,3,9)
Select ANum (0,5)
Type 1, Num 5 should be bigger than 10 In Val2
Type 1, Num 5, should be smaller than 30 In Val2
Type 3, Num 0, should be smaller than 8 In Val1
</code></pre>

<p>I'm using that query to produce results.</p>

<pre><code>SELECT analyse.* FROM analyse 
INNER JOIN 
(SELECT ID FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 &lt; 30) AS a1 ON a1.SID = analyse.SID 
INNER JOIN 
(SELECT SID FROM  WHERE Type = 1 AND Num = 5 AND Val2 &gt; 10) AS a2 ON a2.SID = analyse.SID
INNER JOIN 
(SELECT SID FROM analyse WHERE Type = 3 AND Num = 0 AND Val1 &gt; 8) AS a3 ON a2.SID = analyse.SID 
WHERE Type IN (1,3,9) AND Num IN (0,5) ORDER BY ID, Type, Order, Num ASC

+-----+------+-------+---------------+--------------+--------------+--------------+
| ID  | Type | Order | Val1[Num[0]]  | Val2[Num[0]] | Val1[Num[5]] | Val2[Num[5]] |
+-----+------+-------+---------------+--------------+--------------+--------------+
| ... | ...  | ...   | ...           | ...          | ...          | ...          |
| 118 | 1    | 1     | 10.9000       | 2.2083       | 3.5056       | 15.2627      |
| 118 | 1    | 2     | 9.5000        | 1.9246       | 2.0305       | 11.7049      |
| 118 | 1    | 3     | 7.9000        | 1.6005       | 2.4274       | 16.6597      |
| 118 | 2    | 1     | 10.9000       | 2.2083       | 3.5056       | 15.2627      |
| 118 | 3    | 2     | 20.4000       | 4.1329       | 2.8187       | 22.9676      |
| 118 | 4    | 3     | 28.3000       | 5.7334       | 2.7094       | 29.6273      |
| 119 | 1    | 1     | 27.2000       | 6.8635       | 0.5506       | 14.9084      |
| 119 | 1    | 2     | 25.9000       | 6.5355       | 0.4249       | 10.9550      |
| 119 | 3    | 1     | 27.2000       | 6.8635       | 0.5506       | 14.9084      |
| 119 | 3    | 2     | 53.1000       | 13.3989      | 0.4893       | 25.8634      |
| ... | ...  | ...   | ...           | ...          | ...          | ...          |
+-----+------+-------+---------------+--------------+--------------+--------------+
</code></pre>

<p>Desired output should be like this. However, since there are other Val1 and Val2 values has same ID, same Type but different order, INNER JOIN doesn't work as expected I guess. There are always bigger and smaller values appear than my filter. Some of them never shown.</p>

<p>Sorry If it's too long. I can't find the correct statment or structure. Any advice?</p>

<p>Edit: Sorry. I forgot to mention all other the data must be shown of IDs. That's why I'm trying to use join.</p>