Sqlite, foreign key reference counting

Question

I have two tables, one of which defines lists and the other that defines elements in lists. When I run this, I get lists that have items that reference them. I really need to get the result for all the rows in the list table, and the number of rows in the item table refers to each list.

SELECT name, COUNT(items.listId) as itemCount FROM lists INNER JOIN items ON lists._id = items.listId GROUP BY items.listId 

any help would be greatly appreciated.

Answer 1

Try changing INNER JOIN to LEFT OUTER JOIN and change GROUP BY to lists._id . I have not tested this!