Please help with query question

[NotionCommotion]

I am trying to return the following:

sum (t1.c1*t2.c1)+t3.c1
sum (t1.c2*t2.c2)+t3.c2
where t1.fi=t2.id
where t1.id=t4.id
where t3.id=t4.fi
where t4.id=2

Furthermore, if the value is NULL, then I want to return 0. I am thinking something similar to the following, but t3 is not included if t1 has no matches, and think I might need an outer join. Can anyone please help? Thanks!

SELECT
COALESCE(SUM(t1.c1*t2.c1)+t3.c1,0),
COALESCE(SUM(t1.c2*t2.c2)+t3.c2,0)
FROM t4 AS t4
JOIN t1 AS t1 ON t1.id=t4.id
JOIN t2 AS t2 on t2.id=t1.fi
JOIN t3 AS t3 on t3.id=t4.fi
WHERE t4.id=2;

t1
id fi c1 c2
01 01 02 13
02 01 21 02
02 02 43 31
03 02 01 02
03 03 62 53


t2
id c1 c2
01 11 12
02 23 13
03 42 41
04 12 41

t3
id c1 c2
01 23 44
02 33 12
03 44 11

t4
id f1 c1 c2
01 01 23 44
02 01 33 12
03 03 44 11

[NotionCommotion]

I think I found a solution. In addition to the location of the OUTER JOIN, I also had problems with COALESCE.

SELECT
COALESCE(SUM(t1.c1*t2.c1),0)+t3.c1,
COALESCE(SUM(t1.c2*t2.c2),0)+t3.c2
FROM t4 AS t4
JOIN t3 AS t3 on t3.id=t4.fi
LEFT OUTER JOIN (t1 AS t1 INNER JOIN t2 AS t2 on t2.id=t1.fi) ON t1.id=t4.id
WHERE t4.id=2;