MehdiB MehdiB - 29 days ago 8
MySQL Question

MySQL - using LEFT JOIN gives unexpected results

I have two tables orderedproducts and productsinstocks. here is the table structures:

orderedproducts table:

+-----+---------+-----------+----------+--------------+
| 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 |
+-----+---------+-----------+----------+--------------+


productsinstocks table:

+----+---------+-----------+----------+
| id | stockid | productid | quantity |
+----+---------+-----------+----------+
| 20 | 903958 | 265385 | 200 |
| 21 | 903958 | 407454 | 205 |
| 22 | 903958 | 975045 | 210 |
+----+---------+-----------+----------+


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:

+---------+-----------+---------+-----------+------------+
| 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 |
+---------+-----------+---------+-----------+------------+


I used the following query:

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


but it gives me the following result:

+---------+-----------+---------+-----------+------------+
| orderid | productid | stockid | order_qty | stock_qty |
+---------+-----------+---------+-----------+------------+
| 1016186 | 265385 | 903958 | 5 | 200 |
| 1016186 | 407454 | 903958 | 6 | 205 |
| 1016186 | 975045 | 903958 | 7 | 210 |
+---------+-----------+---------+-----------+------------+


I don't know What I am missing.

Answer

The following condition in your WHERE clause is responsible for the missing record in your result set:

WHERE plo.stockid = 903958

The missing record in the orderedproducts table does not match to any record in the productsinstocks table. Therefore the stockid value for this record will be NULL and your current WHERE condition will filter it off.

Instead, add a check OR plo.stockid IS NULL to the WHERE clause to retain this record:

SELECT op.orderid, op.productid, plo.stockid, op.quantity order_qty,
       plo.quantity AS stock_qty
FROM orderedproducts op
LEFT JOIN productsinstocks plo
    ON op.productid = plo.productid
WHERE op.orderid = 1016186 AND
      (plo.stockid = 903958 OR plo.stockid IS NULL)

Demo here:

SQLFiddle

Comments