CODEWITHSUNDEEP
mysql
Nikk
Nikk

Reputation: 7889

MySQL SUM values from 2 tables and JOIN

I have two tables and both table2 and table3 may contain an amount for the key from table1.

I would like to sum up these values and represent them as amount.

SELECT table1.mg_key
     , table1.mg_name
     , table1.time
     , table2.mg_amount + table3.mg_amount amount
     , table2.mg_key + table.mg_key
  FROM table
  LEFT 
  JOIN table2
     , table3 
    ON table1.mg_key = key 
   AND key = amount

It doesn't work, so Im probably doing something wrong?

Upvotes: 0

Views: 69

Answers (2)

GuiSim
GuiSim

Reputation: 7559

http://sqlfiddle.com/#!9/65dca/1/0

SELECT   
table1.mg_key, table1.mg_name, table1.time,
IFNULL(table2.mg_amount,0) + IFNULL(table3.mg_amount,0) as amount
FROM table1
LEFT JOIN table2 ON table1.mg_key = table2.mg_key
LEFT JOIN table3 ON table1.mg_key = table3.mg_key;

If this does not answer your question, please create a SQL Fiddle to help us better understand.

Upvotes: 1

Andrew
Andrew

Reputation: 7768

You should not use 'key', 'time' as these are reserved words. Sharing your table schemas or creating a fiddle would help.

SELECT 
table1.mg_key, table1.mg_name, table1.time,--time= pick other name
IFNULL(table2.mg_amount,0) + IFNULL(table3.mg_amount,0) as amount,
IFNULL(table2.mg_key,0) + IFNULL(table1.mg_key,0) as pkey
FROM table1
LEFT JOIN table2
ON table1.mg_key = key --key= reserved; pick other name
LEFT JOIN table3 
ON key = amount --key= reserved; pick other name

Upvotes: 1

Related Questions