强制联接的第一个表中的所有行 [英] 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屋!