PostgreSQL multiple join

I would like to make multiple joins in one query, I have four tables for that with the following structure:

Table 1 fields, t1: did (int), finished (datetime), userid (int)

Table 2 fields, t2: userid (int), name

Table 3 fields, t3: blid (int), did (int), count (int), btid(int)

Table 4 fields, t4: btid(int), denom (int)

I want to make a query where I would get all fields of t1 except for that one joined with t2 in that case I would like to show t2.name when t1.userid = t2.userid This is fair simple:

select t1.did, t1.finished, t2.name, t2.userid from t1 inner join on t1.userid=t2.userid

But I also want to add a column composed by the following query:

select sum(t3.count*t4.denom) from t3 inner join t4 on t3.btid=t4.btid

Only when t3.did=t1.did I don’t know if it would be an outer join or otherwise, but I want to add the previous query as a column in the first query and it would be something like:

t1.did, t1.finished, t2.name, t2.userid, “total”?

The real-world problem is that users make money deposits t1 users information are held in t2. The amount deposited is described in t3 for every deposit t1.did and related denominations of each deposit breakdown, in t3 are described in t4.

Answer

You might find this easiest as a correlated subquery:

select t1.did, t1.finished, t2.name, t2.userid,
       (select sum(t3.count * t4.denom)
        from t3 inner join
             t4
             on t3.btid = t4.btid
        where t3.did = t1.did
      ) as value
from t1 inner join  t2
     on t1.userid = t2.userid;

For this version, you want indexes on t3(did, btid, count) and t4(btid, denom).

You can also phrase this as a join and group by:

select t1.did, t1.finished, t2.name, t2.userid,
       sum(t3.count * t4.denom)
from t1 inner join  t2
     on t1.userid = t2.userid left join
     t3
     on t3.did = t1.did left join
     t4
     on t3.btid = t4.btid
group by t1.did, t1.finished, t2.name, t2.userid;