CODEWITHSUNDEEP
mysql
Mc Gyver Basaya
Mc Gyver Basaya

Reputation: 153

create view and perform an addition from multiple tables in mysql

Creating view and add the column value from multiple tables. is that possible?

Users:
ID | FIRST NAME | LASTNAME |
1  | Mark       | Lupez    |
2  | James      | Yap      |

table1:
USER_ID | SCORE | GOAL |
   1    |   7   |   9  |
   2    |   4   |   6  |



table2:
USER_ID | SCORE | GOAL |
   1    |   4   |   9  |
   2    |   7   |   3  |


table3:
USER_ID | SCORE | GOAL |
   1    |   2   |   3  |
   2    |   3   |   2  |

I want to add

table1.score, table2.score, table3.score

where the

table1.user_id, table2.user_id, table3.user_id

are equals to the ID column of the Users table.

so the query results that created by view table is look like this...

LASTNAME  |  FIRST NAME  |  SCORE  | GOAL  |
Lupez     |  Mark        |  13     | 21    |
Yap       |  James       |  14     | 11    |

I am new in mysql so my knowledge about mysql syntax is absolutely basic. adding from multiple tables is a very huge problem for me...

Upvotes: 0

Views: 691

Answers (1)

Chanom First
Chanom First

Reputation: 1136

I make some query from your sample data try this query

(I don't know why you split to 3 tables I think it's not a good design)

    SELECT u.ID
    ,u.LastName
    ,u.FirstName
    ,result.SCORE
    ,result.GOAL
FROM Users u 
LEFT JOIN (
    SELECT q.USER_ID ,SUM(q.SCORE) AS 'score' , SUM(q.GOAL) AS 'goal' 
    FROM (
        SELECT t1.USER_ID,t1.SCORE,t1.GOAL
        FROM table1 t1
        UNION
        SELECT t2.USER_ID ,t2.SCORE,t2.GOAL
        FROM table2 t2
        UNION
        SELECT t3.USER_ID,t3.SCORE,t3.GOAL
        FROM table3 t3
    ) q
    GROUP BY q.USER_ID 
) result 
ON result.USER_ID = u.ID

Upvotes: 3

Related Questions