SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE features
             (featureid varchar(16),
              title varchar(16));
INSERT INTO features
            (featureid,
             title)
            VALUES ('feature1',
                    'Feature 1'),
                   ('feature2',
                    'Feature 2'),
                   ('feature3',
                    'Feature 2');
3 rows affected

CREATE TABLE tasks
             (featureid varchar(16),
              workstreamid varchar(16),
              title varchar(16));
INSERT INTO tasks
            (featureid,
             workstreamid,
             title)
            VALUES ('feature1',
                    'workstream1',
                    NULL),
                   ('feature2',
                    'workstream2',
                    NULL),
                   ('feature2',
                    'workstream3',
                    NULL),
                   ('feature2',
                    'workstream3',
                    NULL);
4 rows affected

CREATE TABLE jobs
             (featureid varchar(16),
              workstreamid varchar(16),
              title varchar(16));
INSERT INTO jobs
            (featureid,
             workstreamid,
             title)
            VALUES ('feature3',
                    'workstream1',
                    NULL),
                   ('feature2',
                    'workstream2',
                    NULL),
                   ('feature2',
                    'workstream3',
                    NULL);
3 rows affected

SELECT f.featureid,
       coalesce(t.workstreamid, j.workstreamid) workstreamid,
       coalesce(t.count, 0) taskcount,
       coalesce(j.count, 0) jobcount
       FROM features f
            LEFT JOIN ((SELECT featureid,
                               workstreamid,
                               count(*) count
                               FROM tasks
                               GROUP BY featureid,
                                        workstreamid) t
                       FULL JOIN (SELECT featureid,
                                         workstreamid,
                                         count(*) count
                                         FROM jobs
                                         GROUP BY featureid,
                                                  workstreamid) j
                                 ON t.featureid = j.featureid
                                    AND t.workstreamid = j.workstreamid)
                      ON coalesce(t.featureid, j.featureid)  = f.featureid;


featureid
workstreamid
taskcount
jobcount


feature1
workstream1
1
0

feature2
workstream2
1
1

feature2
workstream3
2
1

feature3
workstream1
0
1
featureid	workstreamid	taskcount	jobcount
feature1	workstream1	1	0
feature2	workstream2	1	1
feature2	workstream3	2	1
feature3	workstream1	0	1