chntgomez chntgomez - 25 days ago 10
Android Question

SQLite Join Table from Subquery

I have the following Scheme on a SQLite Table (TABLE 1):

ID NAME
---------------------------------
1 "NAME 1"
2 "NAME 2"
... ...


The Id from the table is a foreign key in another table like this one (TABLE 2):

ID NAME DEPARTMENT (FROM TABLE 1)
------------------------------------------------------------
1 "PRODUCT 1" 2
2 "PRODUCT 2" 2
3 "PRODUCT 3" 1


I need to return a query with all of the elements in the TABLE 1 and with a new coulumn named "PRODUCTS" which is the count of all the entries in Table 2 where the Department = Table1.ID.

The result would look like this

ID NAME ENTRIES
---------------------------------------------
1 "NAME 1" 1 (SELECT COUNT FROM TABLE 2 WHERE DEPARTMENT = 1)
2 "NAME 2" 2 (SELECT COUNT FROM TABLE 2 WHERE DEPARTMENT = 2)


I dont know how to do this in SQLite. Is it Possible. How could the query would have to be written?

Answer

Join against the second table and group by the first table columns. When you group your result then aggregate functions like count() apply to each group and not the complete result set.

select t1.id, t1.name, count(t2.id) as entries
from table_1 t1
left join table_2 t2 on t1.id = t2.department
group by t1.id, t1.name