强制联接的第一个表中的所有行 [英] Forcing all rows from first table of a join

问题描述

我有三个表，分别是machines存放自动售货机，products存放所有可能的产品，以及machines_products这是两者的交集，给出了特定机器上每个产品线的库存量.如果机器中没有库存产品，则第三表中没有相应的行.

I have three tables, machines holding vending machines, products holding all possible products, and machines_products which is the intersection of the two, giving how many of each product line is stocked in a particular machine. If a product is not stocked in a machine, there is no corresponding row in the third table.

DESCRIBE machines_products;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| machine_id | int(10) unsigned | NO   | PRI | 0       |       |
| product_id | int(10) unsigned | NO   | PRI | 0       |       |
| quantity   | int(10) unsigned | NO   |     | 0       |       |
+------------+------------------+------+-----+---------+-------+

每种产品都有一个类别(想想巧克力棒还是饮料瓶)，一台机器知道它可以出售哪种产品.我想要该类别所有产品的结果表，以及特定机器的数量.我已经知道了:

Each product has a category (think chocolate bars vs. drinks bottles) and a machine knows what category of products it can vend. I want a result table of all products for the category, with a quantity for a specific machine. I have got as far as this:

SELECT products.*, SUM(quantity) qty
FROM products
LEFT JOIN machines_products USING (product_id)
WHERE machine_id=m AND category_id=c
GROUP BY product_id;

问题是，这会过滤掉没有数量的所有行，而我想要的是来自左表的所有行，如果右边没有对应的行，则qty列中的NULL/0-桌子.

The problem is that this filters out all rows where there is no quantity, whereas what I want is all rows from the left table, and NULL/0 in the qty column if there are no corresponding rows in the right-hand table.

顺便说一句:这不是作业问题！我今年30岁，坐在办公室里:o)

BTW: this is not a homework question! I am 30 and sitting in my office :o)

推荐答案

实际上，发布后不久我就找到了答案.诀窍是避免在WHERE子句中指定第三张表的主键中的任一列(即machine_id和product_id).通过在JOIN的ON条件下使用AND，并在此处指定计算机ID，我得到了想要的结果.

Actually I figured out the answer a short while after posting. The trick is to avoid specifying either of the columns from the third table's primary key (i.e. machine_id and product_id) in the WHERE clause. By using an AND in the JOIN's ON condition, and specifying the machine ID there, I get the result I was looking for.

SELECT products.*, quantity
FROM products
LEFT JOIN machines_products
  ON products.product_id=machines_products.product_id
  AND machine_id=m
WHERE category_id=c

在我的情况下，Brendan建议的COALESCE()函数不是必需的，因为我使用PHP的empty()函数检查了值，所以NULL很好.

The COALESCE() function suggested by Brendan was not necessary in my case, since I check the value with PHP's empty() function, so NULL is fine.

事实证明，从来没有需要发布问题时一直在玩的GROUP BY.

As it turns out, there was never a need for GROUP BY, which I had been playing with when posting the question.

这篇关于强制联接的第一个表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！