mysql join 2 个表 - 显示一个表中的所有行 [英] mysql join 2 tables - show all rows from one table

问题描述

我有两张桌子，像这样:表a"包含:

I have two tables, like so: table "a" contains:

id|name

stock1|fullname
stock2|fullname2
stock3|fullname3

表b"包含给定库存的产品数量.

table "b" contains product quantities for given stock.

id|stock_id|quantity|product_id|
1|stock1|3|13
2|stock3|4|13
3|stock1|1|5
4|stock2|2|2

现在我需要组合这两个表，以便每个产品从表a"中获取其库存全名，如果没有为库存指定其数量，它仍将显示数量为 0 的行.

Now I would need to combine those two tables, so that each product takes its stock full name from table "a", and if its quanitity is not given for stock, it would still show the row with the quanitity as 0.

因此，从我的示例来看，product_id 13 将显示为:

So from my example, product_id 13 would show as:

stock|quanitity|product_id|stock_fullname
stock1|3|13|fullname1
stock2|0|13|fullname2
stock3|4|13|fullname3

推荐答案

您应该能够使用 LEFT JOIN 来实现这一点.

You should be able to use a LEFT JOIN to achieve this.

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13

这篇关于mysql join 2 个表 - 显示一个表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！