MySQL - using LEFT JOIN gives unexpected results
<p>I have two tables orderedproducts and productsinstocks. here is the table structures:</p>

<p>orderedproducts table:</p>

<pre><code>+-----+---------+-----------+----------+--------------+
| id  | orderid | productid | quantity | productgroup |
+-----+---------+-----------+----------+--------------+
| 117 | 1016186 |    265385 |        5 | Main         |
| 118 | 1016186 |    407454 |        6 | Main         |
| 119 | 1016186 |    975045 |        7 | Main         |
| 120 | 1016186 |    975046 |        8 | Main         |
+-----+---------+-----------+----------+--------------+
</code></pre>

<p>productsinstocks table:</p>

<pre><code>+----+---------+-----------+----------+
| id | stockid | productid | quantity |
+----+---------+-----------+----------+
| 20 |  903958 |    265385 |      200 |
| 21 |  903958 |    407454 |      205 |
| 22 |  903958 |    975045 |      210 |
+----+---------+-----------+----------+
</code></pre>

<p>As you see, the stock has only three types of products in orderedproducts table. I want to JOIN these tables to achieve the following result:</p>

<pre><code>+---------+-----------+---------+-----------+------------+
| orderid | productid | stockid | order_qty | stock_qty  |
+---------+-----------+---------+-----------+------------+
| 1016186 |    265385 |  903958 |         5 |       200  |
| 1016186 |    407454 |  903958 |         6 |       205  |
| 1016186 |    975045 |  903958 |         7 |       210  |
| 1016186 |    975046 |  903958 |         8 |       NULL |
+---------+-----------+---------+-----------+------------+
</code></pre>

<p>I used the following query:</p>

<pre><code>SELECT op.orderid, op.productid, plo.stockid, op.quantity order_qty,  plo.quantity stock_qty FROM orderedproducts op
LEFT JOIN productsinstocks plo
ON op.productid=plo.productid
WHERE op.orderid=1016186
AND plo.stockid=903958
</code></pre>

<p>but it gives me the following result:</p>

<pre><code>+---------+-----------+---------+-----------+------------+
| orderid | productid | stockid | order_qty | stock_qty  |
+---------+-----------+---------+-----------+------------+
| 1016186 |    265385 |  903958 |         5 |       200  |
| 1016186 |    407454 |  903958 |         6 |       205  |
| 1016186 |    975045 |  903958 |         7 |       210  |
+---------+-----------+---------+-----------+------------+
</code></pre>

<p>I don't know What I am missing.</p>