CODEWITHSUNDEEP
mysql
Maki
Maki

Reputation: 319

MySQL - Joining three tables to get the SUM of a column

I have a database that has 3 tables. 1st table hosts the users id and position. The 2nd table hosts the boss/squire id. The 3rd hosts the squire id and its money. Suppose I am table1.id=1. I am a pos1 with 2 squire, table1.id=2 with pos1 and table1.id=3 with pos1, where in this 2 has squires under them. table1.id=4 with pos2 and table1.id=5 with pos3 is both under table1.id=2. Same with table1.id=3.

table1.id=1 ------> table1.id=2 ------> table1.id=4
            |                   |
            |                   |
            |                   ------> table1.id=5
            |
            ------> table1.id=3 ------> table1.id=6
                                |
                                |
                                ------> table1.id=7

I want the result be

sumTotal = 1000

CREATE TABLE Table1 (`id` INT, `pos` VARCHAR(255))
CREATE TABLE Table2 (`id` INT, `id_boss` INT, `id_squire` INT)
CREATE TABLE Table3 (`id` INT, `id_squire` INT, `money` INT)

INSERT INTO Table1 (`id`, `pos`)
VALUES
(1, 'pos1'),
(2, 'pos1'),
(3, 'pos1'),
(4, 'pos2'),
(5, 'pos2'),
(6, 'pos3'),
(7, 'pos3');

INSERT INTO Table2 (`id`, `id_boss`, `id_squire`)
VALUES
(1, 1, 2),
(2, 1, 3),
(4, 2, 4),
(5, 2, 5),
(6, 3, 6),
(7, 3, 7);

INSERT INTO Table3 (`id`, `id_squire`, `money`)
VALUES
(1, 4, 100),
(2, 5, 200),
(3, 6, 300),
(4, 7, 400);

I will be using table1.id=1

to help understand pls click: http://sqlfiddle.com/#!9/e8924/4/0

Upvotes: 2

Views: 47

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You first need a self join to Table2 so that you get the descendant nodes:

SELECT SUM(money)
FROM Table2 AS t21
JOIN Table2 AS t22 ON t21.id_squire = t22.id_boss
JOIN Table3 AS t3 ON t22.id_squire = t3.id_squire
WHERE t21.id_boss = 1

Demo here

Edit:

If you want to also include Table1 in the query then you can put it at the beginning of the JOIN chain:

SELECT SUM(t3.money)
FROM Table1 AS t1
JOIN Table2 AS t21 ON t1.id = t21.id_boss
JOIN Table2 AS t22 ON t21.id_squire = t22.id_boss
JOIN Table3 AS t3 ON t22.id_squire = t3.id_squire
WHERE t1.id = 1

Upvotes: 1

Related Questions