MySQL INNER JOIN from second table (TOP10)

1
votes
$stmt = $conn->prepare('SELECT  a.*, c.*, SUM(a.money+b.RESULT) AS ARESULT
FROM users a 
    INNER JOIN bankaccounts c
        ON a.id = c.owner
    INNER JOIN
    (
        SELECT owner, SUM(amount) AS RESULT
        FROM bankaccounts
        GROUP BY owner
    ) b ON a.id = b.owner
    ORDER BY ARESULT DESC LIMIT 10');

What's problem, it show wrong only one record? I want list max 10 records - like TOP 10 richest who has [money+(all his bankaccounts amount)]

Lets say.. I have 2 tables.

Table: users

ID | username | money

1  | richman | 500

2  | richman2 | 600

Table: bankaccounts

ID | owner | amount

65 | 1     | 50

68 | 1     | 50

29 | 2     | 400

So it would list:

  1. richman2 1000$

  2. richman 600$

2
mysqlsqlselectgroup-bysum

2 Answers

1
votes

Try using a subqueries...

$stmt = $conn->prepare('SELECT  a.*, 
    IFNULL((SELECT SUM(amount) FROM bankaccounts b WHERE b.owner=a.id),0) AS BANK_MONEY,
    (IFNULL(a.money,0) + IFNULL((SELECT SUM(amount) FROM bankaccounts c WHERE c.owner=a.id),0)) AS ARESULT
    FROM users a 
    ORDER BY ARESULT DESC LIMIT 0, 10');

EDIT: Added a field for bank account totals

EDIT2: Added IFNULL to SQL statement in case user is not in BankAccounts table

0
votes

Try this: 

SELECT  a.*, (a.money + b.RESULT) AS ARESULT
FROM users a 
INNER JOIN (SELECT owner, SUM(amount) AS RESULT
            FROM bankaccounts
            GROUP BY owner
          ) b ON a.id = b.owner
ORDER BY ARESULT DESC 
LIMIT 10