What's the best way to get a single record's info from multiple tables?

[imgrooot]

So I have two tables. 

 

Table 1 - Records

Table 2 - Earnings

 

I basically want to retrieve 6 active records from highest to lowest earnings.

 

Here are the table setups.

Records Table

record_id      |   record_name       |   status

1                   record_1                1
2                   record_2                0
3                   record_3                1
4                   record_4                1
5                   record_5                1
6                   record_6                1
7                   record_7                1
8                   record_8                1



--------------------------------------------

Earnings Table

earning_id     |    record_id     |    amount

1                      1               $100
2                      2               $200 
3                      3               $300
4                      4               $400
5                      5               $500
6                      6               $600
7                      7               $700
8                      8               $800
9                      1               $100
10                     1               $100

As you can see I have total of 8 records. Only 7 of them are active. And record_1 has multiple earning rows. This is the tricky part. Normally I can retrieve the records seperatly and the earnings seperatly but I would like to know how can I combine this into a single query to achieve the same result so that I can list 6 active records from highest to lowest earnings?

 

Here is my way so far.

$find_records = $db->prepare("SELECT record_id, record_name, status FROM records WHERE status = :status");
$find_records->bindValue(':status', 1);
$find_records->execute();
$result_find_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find_records) > 0) {
  foreach($result_find_records as $row) {
    $record_id           =	$row['record_id'];
    $record_name         =	$row['record_name'];
    $record_status       =	$row['record_status'];

    $get_earnings = $db->prepare("SELECT amount FROM earnings WHERE record_id = :record_id");
    $get_earnings->bindParam(':record_id', $record_id);
    $get_earnings->execute();
    $result_earnings = $get_earnings->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_earnings) > 0) {
      $ub = 0;
      foreach($result_earnings as $key=>$row) {
        $ub+= $row['deposit'];
      }
      $record_amount = $ub;
    }
  }
} 

Edited December 16, 2017 by imgrooot

[Barand]

JOIN the tables using the record_id

SELECT record_id
     , record_name
     , SUM(amount) as total
FROM
     records
     INNER JOIN
     earnings USING (record_id)
WHERE status = 1
GROUP BY record_id
ORDER BY total DESC

[mac_gyver]

you need to research JOINed queries. this will let you get related data in a single query.

 

if you want 'records' results that do have entries in the earnings table, you would use JOIN. if you want 'records' results, regardless of if they have entries in the earnings table, you would use LEFT JOIN.

 

to get aggregate values, research GROUP BY and the aggregate functions. you would use GROUP BY record_id and select SUM(amount) as total (assuming the column name is amount or is it deposit?)

 

if you want a limited number of rows in the result set (your sample data would give 7 active results, but you have stated you want 6 in two places), you would use ORDER BY total DESC (to get the data in the correct order) and LIMIT 6 (to get the 6 highest.)

 

lastly, your sample data implies you have a $ stored with the values. any currency symbol is a human convention and doesn't mean anything to the database and should not be stored with the values. the amount/deposit column should be a numerical data type, integer if only using whole values (what your sample data shows) or decimal for, well, decimal xxx.xx values.