mysql - MySQL在查询中结合了3张表

我有三张桌子。

StockinId - all shelfs in location
+---------+----------+-------+
| country | location | bin   |
+---------+----------+-------+
| LV      | AL       | A-1-1 |
| LV      | AL       | A-1-2 |
| LV      | AL       | A-1-3 |
| LV      | AL       | A-1-4 |
| LV      | AL       | A-1-5 |
| LV      | AL       | A-1-6 |
| LV      | AL       | A-1-7 |
| LV      | AL       | A-1-8 |
| LV      | AL       | A-1-9 |
+---------+----------+-------+


Calculated - calculated amount which should be on the shelf
+-------------------+----------+-------+
| calculated_needed | location | bin   |
+-------------------+----------+-------+
| 2                 | AL       | A-1-1 |
| 4                 | AL       | A-1-2 |
| 15                | AL       | A-1-3 |
| 5                 | AL       | A-1-4 |
| 22                | AL       | A-1-5 |
+-------------------+----------+-------+

Inventory - actual amount counted physically recounting
+--------+----------+-------+---------------+
| amount | location | bin   | item          |
+--------+----------+-------+---------------+
| 2      | AL       | A-1-1 | 2600000741897 |
| 4      | AL       | A-1-2 | 2600000741897 |
| 5      | AL       | A-1-4 | 2600000999045 |
| 22     | AL       | A-1-5 | 2600000998949 |
| 6      | AL       | A-1-6 | 2600000998925 |
| 99     | AL       | A-1-7 | 2600000998871 |
+--------+----------+-------+---------------+

我想创建一个查询，其中输出将包含calculated表中的所有垃圾箱以及inventory表中具有产品计数的行，即使这些垃圾箱应该为空，也就是现实生活中已经被替换的物品。该示例在下面可见。我已经尝试过，但是不知道要这样做。我可以执行一个查询，在该查询中，我会看到所有具有物理量和计算量的已计算仓位，但我无法输出不在Calculated表中的仓位上错位但已计数的项目。

+-------+----------------+-------------------+
| bin   | physcal_amount | calculated_amount |
+-------+----------------+-------------------+
| A-1-1 | 2              | 2                 |
| A-1-2 | 4              | 4                 |
| A-1-3 | -              | 15                |
| A-1-4 | 5              | 5                 |
| A-1-5 | 22             | 22                |
| A-1-6 | 6              | -                 |
| A-1-7 | 99             | -                 |
+-------+----------------+-------------------+

我衷心希望我已经把问题弄清楚了，可以理解。

最佳答案

如果我正确回答您的问题：

SELECT StockinId.bin, COALESCE(Inventory.amount,'-') as physcal_amount, COALESCE(Calculated.calculated_needed,'-') as calculated_amountFROM StockinIdLEFT JOIN CalculatedON StockinId.bin = Calculated.binLEFT JOIN InventoryON StockinId.bin = Inventory.binWHERE Calculated.calculated_needed IS NOT NULLOR Inventory.amount IS NOT NULL

关于mysql - MySQL在查询中结合了3张表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47177186/