英文:
I want find all records even if not yet popoulated with foreign key
问题
表格 stock：
stkid (主键), name
表格 share：
price, quantity, stkid (外键)
我运行了这个查询，但它只显示已经在 share 表中有记录的股票。我想要显示所有股票，即使在 share 表中没有记录。
select name,
0,
sum(price*quantity) / sum(quantity) as avg,
sum(quantity) as qty
from stock
left join share on share.stkid = stock.stkid
group by stock.stkid
英文:
Table stock:
stkid (pk), name
Table share:
price, quantity, stkid (fk)
I run this query but it shows stocks only that have records in shares already. I want to show all stocks even with no records in shares.
select name,
0,
sum(price*quantity) / sum(quantity) as avg,
sum(quantity) as qty
from stock,
share
where share.stkid = stock.stkid
group by (stock.stkid)
答案1
得分: 0
你可以使用LEFT JOIN语句，即使在share表中没有关联的行，它仍然会选择stock表中的行。
SELECT
name,
0,
sum(price*quantity) / sum(quantity) as avg,
sum(quantity) as qty
FROM stock
LEFT JOIN share ON share.stkid = stock.stkid
GROUP BY stock.stkid
英文:
You can use a LEFT JOIN statement, it will still select the lines from the stock table even if there is no linked row in the share table.
SELECT
name,
0,
sum(price*quantity) / sum(quantity) as avg,
sum(quantity) as qty
FROM stock
LEFT JOIN share ON share.stkid = stock.stkid
GROUP BY stock.stkid
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论