I have two tables, like so: table "a" contains:
我有两个表，如so: table a包含:
id|name
stock1|fullname
stock2|fullname2
stock3|fullname3
table "b" contains product quantities for given stock.
表“b”包含给定库存的产品数量。
id|stock_id|quantity|product_id|
1|stock1|3|13
2|stock3|4|13
3|stock1|1|5
4|stock2|2|2
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.
现在我需要合并这两个表，以便每个产品都从表“a”中获取其股票的全名，如果不给出股票的数量，它仍然会显示为0的行。
So from my example, product_id 13 would show as:
在我的示例中，product_id 13将显示为:
stock|quanitity|product_id|stock_fullname
stock1|3|13|fullname1
stock2|0|13|fullname2
stock3|4|13|fullname3
6 个解决方案
#1
4
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
#2
0
try this:
试试这个:
SELECT stock,COALESCE(quanitity,0),product_id,stock_fullname FROM stock JOIN product
#3
0
You need an outer join so that rows from the a table without a corresponding row in b are still considered. An inner join, by contrast, insists that you have a matching row. If you are pulling a value from the table where you don't have a row, you get NULL. Syntax varies between DBs and there is a distinction made depending on if it's the table on the left or right that gets the fake rows.
您需要一个外部连接，以便仍然考虑来自a表的行，而在b中没有相应的行。相反，内部连接则坚持您有一个匹配的行。如果从没有行的表中提取一个值，就会得到NULL。不同的DBs有不同的语法，根据是左边的表还是右边的表获得了假行而有区别。
see other answers for syntax.
参见其他语法的答案。
#4
0
It sounds like you need to use a LEFT JOIN, although the records with no quantity might show as NULL rather than zero. Something like:
似乎您需要使用左连接，尽管没有数量的记录可能显示为NULL，而不是为零。喜欢的东西:
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.stock_id = b.stock_id
#5
0
I think this query should work for your example:
我认为这个查询应该适用于您的示例:
SELECT a.id stock if(b.quantity IS NULL, 0, b.quantity),
b.product_id, a.name stock_fullname
FROM b
LEFT JOIN a b.stock = a.id
WHERE b.product_id = 13;
#6
0
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
#1
4
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
#2
0
try this:
试试这个:
SELECT stock,COALESCE(quanitity,0),product_id,stock_fullname FROM stock JOIN product
#3
0
You need an outer join so that rows from the a table without a corresponding row in b are still considered. An inner join, by contrast, insists that you have a matching row. If you are pulling a value from the table where you don't have a row, you get NULL. Syntax varies between DBs and there is a distinction made depending on if it's the table on the left or right that gets the fake rows.
您需要一个外部连接，以便仍然考虑来自a表的行，而在b中没有相应的行。相反，内部连接则坚持您有一个匹配的行。如果从没有行的表中提取一个值，就会得到NULL。不同的DBs有不同的语法，根据是左边的表还是右边的表获得了假行而有区别。
see other answers for syntax.
参见其他语法的答案。
#4
0
It sounds like you need to use a LEFT JOIN, although the records with no quantity might show as NULL rather than zero. Something like:
似乎您需要使用左连接，尽管没有数量的记录可能显示为NULL，而不是为零。喜欢的东西:
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.stock_id = b.stock_id
#5
0
I think this query should work for your example:
我认为这个查询应该适用于您的示例:
SELECT a.id stock if(b.quantity IS NULL, 0, b.quantity),
b.product_id, a.name stock_fullname
FROM b
LEFT JOIN a b.stock = a.id
WHERE b.product_id = 13;
#6
0
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