Need help in MySQL query - GROUP BY

I need help in creating a query for my database.

I have 2 tables,

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

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

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

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

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.

Answers


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.

This site explains left, right outer full and types of joins visually.. I liked it :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;

Need Your Help

SQLCe local db in temp- path in connectionstring?

c# sql-server-ce connection-string

I have SQL Ce db in my app, which is included in my app directory. While debugging its OK, but when published and run with setup.exe, it retrieves "file not found" in temporary directory the app is...

How can I get Xcode 6 to indent switch statements properly?

objective-c xcode

When editing an Objective C file, the autocomplete snippet for the Switch statement looks like this.