CODEWITHSUNDEEP
sqlsqlite
pmko
pmko

Reputation: 5131

sqlite, counting foreign key references

I have two tables, one that defines lists and another that defines items in the lists. When i run this i only get lists that have items referencing them. i really need to get a result for all rows in the lists table and a count of how many rows in the items table reference 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 much appreciated.

Upvotes: 7

Views: 3183

Answers (1)

ClosureCowboy
ClosureCowboy

Reputation: 21561

Try changing your INNER JOIN to a LEFT OUTER JOIN, and change your GROUP BY to lists._id. I didn't test this!

Upvotes: 8

Related Questions