JOIN the same column in the same table - MYSQL

Vote count:

1

I have this tables:

TABLE addonlist_final

TABLE addons

First, I have to LEFT JOIN the same addon_id so I can get all the needed details(which I've already done). Then I want to JOIN the column with the same identity(addon_id) in the table then add the quantity to each other.

So far I have this code:

    <?php

include("conn.php");

echo "<table border='1' >";
echo "<tr>";
    echo "<th>Description</th>";
    echo "<th>Price</th>";
    echo "<th>Qty</th>";
    echo "<th>Total Cost</th>";
echo "</tr>";
$totaldue = 0;
$currentaddons = mysql_query("SELECT a.*, af.*
                            FROM addons AS a, addonlist_final AS af
                            WHERE a.addon_id = af.faddon_id and af.ftransac_id='2685'
                            ORDER BY af.timef DESC
                            ");

while($rows = mysql_fetch_assoc($currentaddons)){
    $desc = $rows['description'];
    $price = $rows['price'];
    $qty = $rows['quantity'];
    $totalcost = $price * $qty;
    $totaldue += $price * $qty;
        echo "<tr>";
            echo "<td>$desc</td>";
            echo "<td>$price</td>";
            echo "<td>$qty</td>";
            echo "<td>$totalcost</td>";
        echo "</tr>";
}

echo "</table>";

echo "<h3>Total Due: ".number_format($totaldue)."</h3>";

?>

This shows me this:

What I want to show is:

Is this possible with just one query? Thanks in advance :)

asked 1 min ago

Archie Zineg

68

JOIN the same column in the same table - MYSQL