Count Instances of Value Across Columns in Different Tables

countjoin;

Let's say I have the following tables :

Table 1

task | date 
100  | 2016-01-01
101  | 2016-01-01
103  | 2016-01-01

Table 2

task2 | date 
113   | 2016-01-01
113   | 2016-01-01
103   | 2016-01-01

I would like to get to this result: 

task_result | Count
100         | 0
101         | 0
103         | 1

Basically count data included in task1 table1 present in task2 table2.

Any idea?

Best Answer

You can do this:

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;

Table1 is your base set, so join to it. You want to retain all values, so you use a LEFT JOIN and you join on the matching task values. The group and count operations happen on different columns - the GROUP BY is based on your tasks from Table1, and you COUNT the of tasks from Table2.

Here's a demo (using CTEs as implemented by SQL Server, PostgreSQL, Oracle, etc.):

WITH Table1 AS
(
    SELECT 100 AS Task
    UNION ALL
    SELECT 101
    UNION ALL
    SELECT 103
)
,Table2 AS
(
    SELECT 113 AS Task
    UNION ALL
    SELECT 113
    UNION ALL
    SELECT 103
)

SELECT Table1.Task, COUNT(Table2.Task)
FROM Table1
LEFT JOIN Table2
    ON Table1.Task = Table2.Task
GROUP BY Table1.Task ;
Related Question