views:

97

answers:

2

I have two database tables, 'Lists' and 'Notes'.
Lists has columns _id, listname
Notes has columns _id, checked, list_id (which is a foreign key of Lists._id), and more columns that aren't relevant to this question.

I would like to create a query that returns four columns:
Lists._id, Lists.listname, the count of all checked Notes in this list, the count of all Notes in this list.

The query should return all entries from Lists.

I can get the counts and the _ids & listnames seperately, using the following queries

SELECT _id, listname FROM Lists
SELECT count(checked) FROM Notes WHERE checked='1' and list_id=foo_id
SELECT count(*) FROM Notes WHERE list_id=foo_id

In these statements, foo_id refers to the _id of the list I would like to count checked and total items for.

Could someone show me how I could put these all into one query?

Bonus points for throwing an AS in there for the count columns.

+1  A: 
SELECT _id, listname, 
    (SELECT count(checked) FROM Notes WHERE checked='1' and Notes.list_id=Lists.list_id ) AS count_checked,
    (SELECT count(*) FROM Notes WHERE Notes.list_id=Lists.list_id) as count_all
  FROM Lists 
Ray
that looks like what I would have come up with, but I was looking for something a little more elegant.
CodeFusionMobile
+7  A: 

EDIT: Added IFNULL to return 0 as count_checked when outer join returns no rows; added aliases.

SELECT
  l._id,
  l.listname,
  IFNULL( SUM(n.checked), 0 ) AS count_checked,
  COUNT(*) AS count_total
FROM lists l
LEFT OUTER JOIN notes n ON ( l._id = n.list_id )
GROUP BY l._id, l.listname
Peter Lang
I never though that I could use SUM to get the checked column! Great idea. The GROUP BY was the part I was missing. Thanks
CodeFusionMobile
Ran into one issue with solution. If there are no matches in "notes", SUM(checked) returns null. I need it to return 0 by default.
CodeFusionMobile
@CSharperWithJava: You are right, I added an `IFNULL` to avoid that problem.
Peter Lang