JOIN the same column in the same table - MYSQL

Question

I have this tables:

TABLE addonlist_final

enter image description here

TABLE addons

enter image description here

First, I have to 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:

enter image description here

What I want to show is:

enter image description here

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

Looks like you want the `SUM()` aggregate function rather than another `JOIN` to me : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum — CD001, Jan 27 '15 at 13:24
As an aside `mysql_*` functions are deprecated - don't use them : http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — CD001, Jan 27 '15 at 13:25
@CD001, yeah I'm still practicing `PDO`. So, `mysql_*` is my expression. — Archie Zineg, Jan 27 '15 at 13:39

score 4 · Accepted Answer · answered Jan 27 '15 at 13:25

4

You uave to use group by

SELECT a.description, sum(af.quantity) as quantity,price
FROM addons AS a, addonlist_final AS af
WHERE a.addon_id = af.faddon_id and af.ftransac_id='2685'
group by a.description
ORDER BY af.timef DESC

answered Jan 27 '15 at 13:25

Jens

67,715
15
98
113

2

`group by` is the right answer - but i would rather group by an int field instead of a varchar (e.g. `group by a.addon_id` instead of `group by a.description`) – Ste Bächler Jan 27 '15 at 13:32
2

Then you can not select the description on other dbms like oracle, thats why i have grouped by `a.description` – Jens Jan 27 '15 at 13:34
2

true - i didn't think about other dbms because this question is clearly about mysql - but on other dbms, i'll still at least include the addon_id (e.g. `group by a.addon_id, a.description`) since the description is probably (at least in the db design) not unique – Ste Bächler Jan 27 '15 at 13:36
1

Works like magic. Thanks but how the line `sum(af.quantity) as quantity,price` works? Any link for additional info? – Archie Zineg Jan 27 '15 at 13:37
2

@ArchieZineg see the [mysql documentation](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) – Jens Jan 27 '15 at 13:38

JOIN the same column in the same table - MYSQL

1 Answers1