CODEWITHSUNDEEP
phpmysql
Andrew
Andrew

Reputation: 3613

Unable to return results for MySQL query

My goal is to only show purchase orders that haven't received all their product yet.

I have a master table called test_po and two other tables test_po_bom, test_rog_bom. The test_po_bom and test_rog_bom tables are where I'm storing the list of products. test_po_bom is the list of products I've ordered, test_rog_bom is the list of products I've received.

Basically: loop purchase_orders WHERE products_received < products_ordered

Table Structure:

table `test_po`: `ID`, `vendor_ID`
table `test_po_bom`: `ID`, `po_ID`, `product_ID`, `quantity`
table `test_rog_bom`: `ID`, `po_ID`, `product_ID`, `quantity`

Code:

$SQL = "SELECT
        *,
        test_po.ID AS test_po_ID
      FROM
        test_po
      LEFT JOIN test_po_bom ON test_po_bom.po_ID=test_po.ID
      LEFT JOIN test_rog_bom ON test_rog_bom.po_ID=test_po.ID
      WHERE
        (SELECT SUM(quantity) FROM test_rog_bom WHERE po_ID=test_po.ID) < (SELECT SUM(quantity) FROM test_po_bom WHERE po_ID=test_po.ID)";

$result = mysql_query($SQL) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
  echo $row['test_po_ID'].'<br>';
}

It doesn't spit out anything, and I've tried many different variations but I just can't figure it out.

Upvotes: 1

Views: 85

Answers (2)

Robert
Robert

Reputation: 8767

The problem appears to be with your query. Don't use * and instead specify the desired columns. The following solution uses aliases to help make your code more readable, especially with similar names. You will also notice HAVING instead of WHERE.

SELECT 
    p.ID as PO_ID
     ,p.VENDOR_ID
     ,pb.product_ID as PRODUCT_ID
     ,SUM(pb.quantity) as QUANTITY_ORDERED
     ,SUM(rb.quantity) as QUANTITY_RECEIVED
FROM test_po as p
LEFT JOIN test_po_bom as pb ON pb.po_ID = p.ID
LEFT JOIN test_rog_bom as rb ON rb.po_ID = p.ID
GROUP BY
    p.ID
     ,p.VENDOR_ID
     ,pb.product_ID
HAVING SUM(rb.quantity) < SUM(pb.quantity)

Upvotes: 1

Nate C-K
Nate C-K

Reputation: 5942

THIS IS UNTESTED CODE. I apologize for posting untested code but I can't test it right now, and I think it demonstrates some things for you to try differently even if it's not exactly correct.

Try this:

select po.ID, po_bom.quant n_ordered, rog_bom.quant n_received
from test_po po
left join (select po_ID, sum(quantity) as quant from test_po_bom group by po_ID) po_bom
  on po.ID = po_bom.po_ID
left join (select po_ID, sum(quantity) as quant from test_rog_bom group by po_ID) rog_bom
  on po.ID = rog_bom.po_ID
where coalesce(rog_bom.quant, 0) < coalesce(po_bom.quant, 0);

This changes a few things from how you were doing them:

  • Uses table aliases to clearly specify which references refer to the same table row.
  • Uses group by to aggregate the sums by ID.
  • Uses coalesce to deal with situation where at least one of your tables (probably test_rog_bom) has no rows for an ID. I suspect this was actually the source of your problem in the first place.

Upvotes: 1

Related Questions