SQL Join statement in iReport

Question

I am working on a report that is supposed to display a "PART" and it's quantity of items in both our "WO" (workOrder table) and "SO" (salesOrder table). I believe I have the formatting and everything else completed but for what ever reason I can not seem to get my logic correct with my join statements.

My logic is based off of - Selecting a PART_NUMBER displays the quantity of that part in both a WO and SO. It then adds the total of the columns for SO and WO and then produces a Grand total of each of those.

I am new to SQL and have been working on this report for 4 days and can not seem to get it to work. I am assuming my JOINS are off or something. Any help that can be given will be appreciated!

---

SELECT
 COALESCE(woitem.qtytarget,0) AS woallocated,
 COALESCE(soitem.qtyfulfilled,0) AS soallocated,
 PART."NUM" AS PART_NUM,
 WO."NUM" AS WO_NUM,
 PART."ID" AS PART_ID,
 WO."ID" AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 SO."ID" AS SO_ID,
 SO."NUM" AS SO_NUM,
 WOITEM."ID" AS WOITEM_ID,
 WOITEM."QTYTARGET" AS WOITEM_QTYTARGET,
 SOITEM."ID" AS SOITEM_ID,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 WOITEM."WOID" AS WOITEM_WOID,
 WOITEM."PARTID" AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "WO" WO ON PART."ID" = WO."ID"
 INNER JOIN "SO" SO ON PART."ID" = SO."ID"
 INNER JOIN "WOITEM" WOITEM ON PART."ID" = WOITEM."PARTID"
 AND WO."ID" = WOITEM."WOID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum}

I am new to SQL and am grateful for any help in my logic!

Answer 1

Try using this.. You're trying to inner join tables that arent really related so you're getting duplicates.. create 2 queries and Union them with UNION ALL

SELECT
 COALESCE(woitem.qtytarget,0) AS woallocated,
 0 AS soallocated,
 PART."NUM" AS PART_NUM,
 WO."NUM" AS WO_NUM,
 PART."ID" AS PART_ID,
 WO."ID" AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 NULL AS SO_ID,
 NULL AS SO_NUM,
 WOITEM."ID" AS WOITEM_ID,
 WOITEM."QTYTARGET" AS WOITEM_QTYTARGET,
 NULL AS SOITEM_ID,
 0 AS SOITEM_QTYFULFILLED,
 WOITEM."WOID" AS WOITEM_WOID,
 WOITEM."PARTID" AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "WOITEM" WOITEM ON PART."ID" = WOITEM."PARTID"
 INNER JOIN "WO" WO ON WOITEM."WOID" = WO."ID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum}

UNION ALL

SELECT
 0 AS woallocated,
 COALESCE(soitem.qtyfulfilled,0) AS soallocated,
 PART."NUM" AS PART_NUM,
 NULL AS WO_NUM,
 PART."ID" AS PART_ID,
 NULL AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 SO."ID" AS SO_ID,
 SO."NUM" AS SO_NUM,
 NULL AS WOITEM_ID,
 0 AS WOITEM_QTYTARGET,
 SOITEM."ID" AS SOITEM_ID,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 NULL AS WOITEM_WOID,
 NULL AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "PRODUCT" P ON PART."ID" = P."PARTID"
 INNER JOIN "SOITEM" SOITEM ON P."ID" = SOITEM."PRODUCTID"
 INNER JOIN "SO" SO ON SOITEM."SOID" = SO."ID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum} 

Select only the WO items in the first query, then join that to the SO items.. SO fields will be empty for WO items and vise versa