user1294998 user1294998 - 3 months ago 9
Android Question

How to query number of items from two tables?

My database has a Manifest table and an Item table which Manifest can have 1 to many Item but 1 Item can only has 1 Manifest. The tables' structure is as below:

Manifest Table

ID Name
1 Bill_1
2 Bill_2


Item Table

ID Name Manifest_ID (FK)
1 Item_1 1
2 Item_2 1
3 Item_3 2


What is the query so that I could get the following result?

Query Result

Manifest_ID Manifest_Name Number of Item
1 Bill_1 2
2 Bill_2 1

Answer

Use inner join and group by:

SELECT  Manifest.ID, Manifest.Name, count(Manifest_ID) FROM Manifest INNER JOIN Item ON Manifest.ID = Item.Manifest_ID group by Manifest_ID;