如何将具有相同属性的两个不同表的值组合在一起

Question

I'm working with a project which includes to display the carid(foreign key from tbl_vehicle) reg_num(registration number or plate number of a car, from tbl_vehicle) and amount(from tbl_fuel), I successfully display the values of carid, reg_num and amount from the table tbl_fuel and tbl_vehicle in HTML form using this SQL statement.

$Withdraw = query("SELECT tbl_fuel.carid,tbl_vehicle.reg_num,sum(trim(replace(amount, '$', '')) + 0.0) as amount
                   FROM tbl_fuel
                    LEFT JOIN tbl_vehicle
                    on tbl_fuel.carid=tbl_vehicle.carid
                    GROUP BY carid");

but I forgot that there is another table named tbl_maintenance with the same attributes to tbl_fuel, which are carid(foreign key from tbl_vehicle), amount. I need to display the values of this attributes fromtbl_fuel and tbl_maintenancein single HTML form.

this is my html form

<div class="panel-body">
    <h3 align="center">Withdrawal Per Vehicle</h3>
    <table class="table table-striped table-bordered">
        <thead>
            <tr>
                <th>ID</th>
                <th>Plate Number</th>
                <th>Amount</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($Withdraw as $w): ?>
                <?= '<tr>' ?>
                    <?= '<td>' . $w["carid"] . '</td>' ?>
                    <?= '<td>' . $w["reg_num"] . '</td>' ?>
                    <?= '<td>' . $w["amount"] . '</td>' ?>
                <?= '</tr>' ?>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

note that the codes stated above runs properly, my question is, what SQL line should be added to my current SQL statement to include attribute's value from tbl_maintenance and display it in my HTML form and group the carid of tbl_maintenance and tbl_fuel and sum the amount from tbl_maintenance and tbl_fuel?

Answer 1

Switch it around, make tbl_vehicle the main table, and do sub-queries for the sums:

SELECT v.carid,
       v.reg_num,
       IFNULL(f.sum_amount,0) + IFNULL(m.sum_amount,0) AS amount
FROM tbl_vehicle v
LEFT JOIN
  (SELECT carid,
          sum(trim(replace(amount, '$', ''))+0) sum_amount
   FROM tbl_fuel
   GROUP BY cardid) f ON f.carid = v.carid
LEFT JOIN
  (SELECT carid,
          sum(trim(replace(amount, '$', ''))+0) sum_amount
   FROM tbl_maintenance
   GROUP BY cardid) m ON m.carid = v.carid
WHERE f.sum_amount IS NOT NULL OR m.sum_amount IS NOT NULL