在MySQL查询中需要帮助 - GROUP BY

I need help in creating a query for my database.

我需要帮助为我的数据库创建一个查询。

I have 2 tables,

我有2张桌子，

asset which consists of (hostname, owner, engineer, date, branch)

由（主机名，所有者，工程师，日期，分支）组成的资产

branch which consists of (id, name, address, region)

由（id，name，address，region）组成的分支

branch.id is a primary key which is connected to asset.branch which is obviously a foreign key.

branch.id是一个连接到asset.branch的主键，它显然是一个外键。

I wanted to create a query that displays the branch information (id, name, address, region) and the number of assets in that branch.

我想创建一个查询，显示分支信息（id，name，address，region）和该分支中的资产数量。

I tried this query:

我试过这个查询：

SELECT b.id, b.name, b.address, b.xcor, b.ycor, b.status, 
  COUNT(a.hostname) AS noofasset
FROM branch b, asset a 
WHERE a.branch = b.id
GROUP BY b.id;

It worked at a glance, but failed to display branch information that has no assets in it. I need a query that also displays branches that have 0 asset.

它一目了然，但未能显示没有资产的分支信息。我需要一个查询，它也显示有0资产的分支。

1 个解决方案

#1

SELECT b.id, b.name, b.address, b.xcor, b.ycor, b.status, 
  COUNT(a.hostname) AS noofasset
FROM branch b
LEFT JOIN  asset a 
  on a.branch = b.id
GROUP BY b.id, b.name, b.address, b.xcor, b.ycor, b.status

LEFT join says include all branches regardless if an asset exists.

LEFT join表示包括所有分支，无论资产是否存在。

This site explains left, right outer full and types of joins visually.. I liked it :D

这个网站在视觉上解释左，右外部完整和连接类型..我喜欢它：D

Using the syntax you're more familiar with (I think)

使用您更熟悉的语法（我认为）

SELECT b.id, b.name, b.address, b.xcor, b.ycor, b.status, 
  COUNT(a.hostname) AS noofasset
FROM branch b, asset a 
WHERE a.branch(+) = b.id
GROUP BY b.id;

#1

SELECT b.id, b.name, b.address, b.xcor, b.ycor, b.status, 
  COUNT(a.hostname) AS noofasset
FROM branch b
LEFT JOIN  asset a 
  on a.branch = b.id
GROUP BY b.id, b.name, b.address, b.xcor, b.ycor, b.status