COUNT() and link between tables

Question

I have some difficulties to make a query.

Basically, I have a 3 tables: member, build and up:

member
--+---
id|...

build
--+-----------+---
id|member (fk)|...

up
--+-----------+---------
id|member (fk)|build (fk)

How can I get the total of "up" for each member (build owner)? I only know how to get it for each build:

SELECT up.build, COUNT(*) as totalUp FROM up GROUP BY build

To be clear, I'm not asking about this query:

SELECT up.member, COUNT(*) as totalUp FROM up GROUP BY member

More details

In my application, a member can "up" (it's a kind of "like") a build. When a member up a build, there is a new entry in the table up with the id of the build liked and the id of the member who liked the build. Each member can have a build.

Globally I want to get the total of likes for each build owner.

Example:

member
--+---
id|
0 |
1 |
2 |

build
--+------+---
id|member|...
--+------+---
0 | 1
1 | 1
2 | 2

up
--+------+-----
id|member|build
--+------+-----
0 |0     |0
1 |0     |1
2 |0     |2

The result of the query should be:

memberID|totalUp
--------+-------
1       |2
0       |0
2       |1

Answer 1

SELECT
    A.id build_owner,
    COUNT(C.build) build_member_count
FROM member A
INNER JOIN build B ON A.id = B.member       -- or LEFT joins
INNER JOIN up    C ON B.id = C.build        -- 
GROUP BY A.id ;