sql - Counting number of rows with foreign keys -

i using sqlite3 on android. trying find number of rows related each row in result. following reflects general organization of tables, details more complex:

dept:

+----+-----------------+ | id | name            |  +----+-----------------+ 

employee:

+----+-----------------+-----------------+ | id | dept_id         | name            | +----+-----------------+-----------------+ 

manager:

+----+-----------------+-----------------+ | id | dept_id         | name            | +----+-----------------+-----------------+ 

i trying query of departments, , each 1 display count of 1) employees , 2) managers. result might like:

id | name | num_employees | num_managers 1  | ibm  | 10000         | 800 2  | fb   | 8000          | 20 

i in 1 query can use single cursorloader this. i've tried this, it's not right:

select d.id, d.name, count(e.id), count(m.id) dept d  inner join employee e on d.id = e.dept_id  inner join manager m on d.id = m.dept_id  group d.id; 

thanks in advance help!

joining both employee , manager results in all possible combinations of employees , managers. use count(distinct) count unique occurrences of these:

select d.id,        d.name,        count(distinct e.id) num_employees,        count(distinct m.id) num_managers dept d join employee e on d.id = e.dept_id join manager  m on d.id = m.dept_id group dept.id 

however, generating these combinations, filter them out later, inefficient.

you compute counts separately, correlated subqueries:

select id,        name,        (select count(*)         employee         dept_id = dept.id        ) num_employees,        (select count(*)         manager         dept_id = dept.id        ) num_managers dept