MySQL - Sum from another table

Question

I want to run a select statement that includes a sum from another table. I know I will probably have to setup a join but I'm not sure how to approach it.

I want to select a list of accounts then get their balances(sum from another table) This is the idea I have been going in but obviously its not correct.

SELECT
    account_name
FROM
    tblaccounts
    JOIN (
        SELECT
            SUM(balance) AS account_balance
        FROM
            tblinvoices
    ) t ON t.account_id = tblaccount_account_id
WHERE
    tblaccounts.account_id = 1

desired output

Name   |   balance
------------------
Account1      50
Account2     100

Answer 1

Try

SELECT account_name, SUM(balance) balance
 FROM tblaccounts a LEFT JOIN tblinvoices i
   ON a.account_id = i.tblaccount_account_id
WHERE a.customer_id = 1
GROUP BY account_id

Output:

| ACCOUNT_NAME | BALANCE |
--------------------------
|     Account1 |      50 |
|     Account2 |     100 |

Here is SQLFiddle demo

Answer 2

You will need to create sub-queries to isolate each SUM otherwise you will return a SUM of every balance, not one for each. And it will be one row, not a row for each account.

SELECT 
     a.account_name,
    (
      SELECT SUM(i.balance) 
      FROM tblinvoices AS i 
      WHERE a.account_id = i.tblaccount_account_id
    ) AS balance
FROM tblaccounts AS a;