Get the number of specific instances in an inner join?
I have three tables: users, lists and tasks.
The list table has user_id as a foreign key. The task table has list_id as foreign key and user_id as field.
I am using the following SQL statement to display lists of users and the number of completed tasks:
SELECT
list.list_id,
list_name,
list_time_created,
COUNT( task.task_id ) AS numberofTasks,
COUNT( CASE WHEN task.task_completed=1 THEN 1 END) AS numberofCompletedTasks
FROM list
INNER JOIN task
ON list.user_id = ':user_id' AND list.list_id = task.list_id
GROUP BY list_name
The complete table schemas are:
list
- list_id
- LIST_NAME
- user_id FK
a task
- TASK_ID
- task_name
- task_completed
- user_id
- list_id FK
This does not work. How can I modify this statement to get all the lists from the user (even if the list does not contain tasks) and get the number of tasks (total and completed), if any?
source to share
Part of your problem is that you are only checking the user_id on the list table. You should also make sure that the task table also contains the correct user_id. One way to do this is to join tables to that condition and then check for a specific user in a where clause.
Second, if you want to see lists regardless of whether or not they have tasks, you must use an outer join. If you do, I recommend modifying your aggregate functions to handle null values. Try something like this:
SELECT
l.list_id, l.list_name, l.list_time_created,
COUNT(CASE WHEN t.task_id IS NULL THEN 0 ELSE 1 END) AS numberOfTasks,
SUM(CASE WHEN t.task_completed IS NULL THEN 0 ELSE t.task_completed END) AS numberOfCompletedTasks
FROM list l
LEFT JOIN task t ON l.list_id = t.list_id AND l.user_id = t.user_id
WHERE l.user_id = 1
GROUP BY l.list_id;
This will select columns, count all non-zero instances of task_id, all instances where task_completed is 1 for each list_id. This will show all list_ids, so if it has no tasks, we will see the counts of 0. There is no need to do additional grouping of user_id here as you are asking for a specific user.
Since task_completed is either 0 or 1, using the SUM aggregation function effectively gives you the number of completed tasks. I used validation, although I summed from 0 for null instances.
This is worked out in the SQL Fiddle for me, let me know if this is causing problems for you.
EDIT
If I can comment on the design of your table, I would recommend removing the user_id from one of the tables. You don't need to have it in both. Let's say you are removing user_id from the task table. I can go from the task-> list to get the user_id. If I have this in both, they might be inconsistent. However, this is just a comment. I don't know your whole situation, a design change might not be practical, or maybe you have a reason for doing this.
source to share