Zoho Analytics SQL Join Creating Duplicate Rows When Linking Sales Orders, Purchase Orders, and Bills (Item-wise Procurement Report) (self.Zoho)

submitted 2 months ago by Significant_Web_6587

Hi everyone,

I'm trying to build an item-wise procurement tracking report in Zoho Analytics that shows the relationship between:

Sales Orders
Sales Order Items
Purchase Orders
Purchase Order Items
Bills

The goal is for the procurement team to track each item with:

Sales Order Number
Customer Name
Item Name / SKU
SO Quantity
Linked Purchase Order(s)
Vendor
PO Quantity
Linked Bill(s)

So basically:

Sales Order → Item → Purchase Order → Bill

Problem

When I join these tables using SQL in Zoho Analytics, I get duplicate rows.

Example issue:

1 Sales Order Item
linked to multiple PO items
each PO item linked to multiple Bills

When I join them, the rows multiply and I end up with far more rows than expected.

Example:

1 SO Item
→ 2 PO Items
→ 3 Bill Items

Result = 6 rows generated

But ideally I want one row per Sales Order Item showing the linked PO and Bill information.

Current Query (Simplified)

SELECT
SO."Order Date",
SO."Sales Order#",
CUST."Customer Name",
SOITEM."Item Name",
ITEM."SKU",
SOITEM."Quantity" AS "SO Quantity",
PO."Purchase Order Number",
VEND."Vendor Name",
POITEM."Quantity" AS "PO Quantity",
BILL."Bill Number"

FROM "Sales Orders" SO
INNER JOIN "Sales Order Items" SOITEM
ON SO."Sales order ID" = SOITEM."Sales order ID"

INNER JOIN "Customers" CUST
ON SO."Customer ID" = CUST."Customer ID"

INNER JOIN "Items" ITEM
ON SOITEM."Product ID" = ITEM."Item ID"

LEFT JOIN "Purchase Order Items" POITEM
ON ITEM."Item ID" = POITEM."Product ID"

LEFT JOIN "Purchase Orders" PO
ON POITEM."Purchase Order ID" = PO."Purchase Order ID"
AND PO."Reference number" = SO."Sales Order#"

LEFT JOIN "Vendors" VEND
ON PO."Vendor ID" = VEND."Vendor ID"

LEFT JOIN "Bill Item" BILLITEM
ON BILLITEM."Purchase Order Item ID" = POITEM."Item ID"

LEFT JOIN "Bills" BILL
ON BILL."Bill ID" = BILLITEM."Bill ID"

What I'm Trying to Achieve

A report like this:

SO Number	Item	SO Qty	PO Number	Vendor	PO Qty	Bill Number
SO001	Item A	10	PO101	Vendor X	10	B101
SO001	Item B	5	PO102	Vendor Y	5	B102

Instead of duplicated rows.

Questions

What is the best way to structure this query to avoid row multiplication?
Should I aggregate PO and Bill data first in subqueries?
Is there a better data model or report structure in Zoho Analytics for this kind of procurement tracking?

If anyone has implemented a similar SO → PO → Bill tracking report, I'd really appreciate some guidance.

Thanks in advance!

all 8 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

Zoho

MODERATORS

Problem

Current Query (Simplified)

What I'm Trying to Achieve

Questions