Comparing SUM of two tables

[tracerstevens]

Hi Everyone,
This one is driving me crazy.

I have three tables: purchase_order, purchase_order_bom, and rog_bom.

The table purchase_order_bom is a list of parts ordered for the purchase order. The table rog_bom is a list of parts received for the purchase order (ROG stands for receipt of goods).

I want to run a query that looks through table purchase_order but only show the purchase orders that have received less parts than the total number of parts order. Basically the purchase orders that haven't been fully received.

table purchase_order:
ID INT

table purchase_order_bom:
ID INT
po_ID INT
quantity INT
product_ID INT

table rog_bom:
ID INT
po_ID INT
quantity INT
product_ID INT

I thought it would be something like this (but this shows only one purchase_order row):

SELECT
  *,
  po.ID AS po_ID
FROM
  purchase_order po
WHERE
  (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID) < (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID)
 

Thank you!
Andy

[Rabbit]

You need to flip your conditional. Right now you're only returning the ones that received more than they ordered.

[tracerstevens]

Hi Rabbit,

Good eye, I've changed it around so that ROG total is first. So it checks if ROG_bom < PO_bom. But it still only shows one PO, which actually has the ROG_bom total equeal to PO_bom, so it doesn't make sense.

There should be about 30 PO's that have less items received than ordered.

Am I writing the query correctly? Or am I totally off base?

Thanks!
Andy

[Rabbit]

What's does your current SQL look like?

[tracerstevens]

SELECT
 *,
 po.ID AS po_ID
FROM
 purchase_order po
WHERE
 (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID) < (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID)
 

Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy

[tracerstevens]

SELECT
 *,
 po.ID AS po_ID
FROM
 purchase_order po
WHERE
 (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID) < (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID)
 

Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy

[Rabbit]

That should work. Post some sample data of the records that should show up but aren't showing up.

[tracerstevens]

Rabbit,
As I was putting together some sample data for you I figured out that the query above does work but not when I applied it to my real data.

I found out that if there are no ROGs, it returns NULL instead of 0, so I had to use:

SELECT COALESCE(SUM(quantity),0) FROM rog_bom WHERE po_ID=po.ID

Thanks for your help, you got me to the solution