Can't find the right query....

Ok, here's a snapshot of my datausers
+----+------------+
| id | Type |
+----+------------+
| 1 | Apprentice |
| 2 | LANWizard |
| 3 | Admin |
| 4 | Apprentice |
+----+------------+

todo
+----+-----+--------+
| id | uid | apprID |
+----+-----+--------+
| 1 | 3 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 0 | <-- Unassigned todo item
+----+-----+--------+Ok, todo.uid is the ID number of the user that the todo item belongs to. todo.apprID is the ID number of the Apprentice that the todo item has been assigned to. I'd like to get the following result from 1 query+--------+--------------+
| apprID | num_of_todos |
+--------+--------------+
| 1 | 2 |
| 4 | 0 |
+--------+--------------+But I can't seem to conjure up the correct syntax to get this done with just 1 query. As you can see I'm using the defualt of 0 for a todo item that hasn't been assigned to an Apprentice. Should this column accept NULL instead?

Follow up:
I've been working on this and here are some results I'm gettingSELECT u.id as apprID, count(apprID) as num_of_todos
FROM users u, todo t
WHERE u.Type = 'Apprentice' and apprID != 0
GROUP BY u.id

result
+--------+--------------+
| apprID | num_of_todos |
+--------+--------------+
| 1 | 2 |
+--------+--------------+

SELECT u.id as apprID, count(apprID) as num_of_todos
FROM users u, todo t
WHERE u.Type = 'Apprentice'
GROUP BY u.id

results
+--------+--------------+
| apprID | num_of_todos |
+--------+--------------+
| 1 | 3 |
| 4 | 3 |
+--------+--------------+

SELECT u.id as apprID, count(apprID) as num_of_todos
FROM users u, todo t
WHERE u.id = t.apprID
GROUP BY u.id

result
+--------+--------------+
| apprID | num_of_todos |
+--------+--------------+
| 1 | 2 |
+--------+--------------+

I believe that in this particular case you need LEFT JOINs to get also those values listed that don't have a matching ON condition with the joined table.


SELECT

users.id AS apprID,
COUNT(todo.apprID) AS num_of_todos

FROM users
LEFT JOIN todo ON users.id = todo.apprID

WHERE users.type = 'Apprentice'

GROUP BY todo.apprID

ORDER BY users.id ASC;


gave on my machine with two tables set up exactly like your test data the result


+--------+--------------+

| apprID | num_of_todos |

+--------+--------------+

| 1 | 2 |

| 4 | 0 |

+--------+--------------+


and also scaled well for more users + correlating todo entries.

You might double check the SQL, left joins aren't really my strength, I'll still find them occasionally quite daunting... as in this case. But not impossible.

Cheers mordred. I acutally got this figured out a bit ago, but it's good to have some confirmation that I got it right. It's good to know this will scale well, because there will eventually be 3000+ users each with an indefinite number of customers, and an undetermined amount of apprentices, all of which can use the todo list functions.

:thumbsup: