Search the Community

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; } } }