Member Avatar for jared.geli

Hi guys I tried to to use inner join on 5 tables it worked but the problem is instead of getting the exact sum values it got doubled. Here's my code

cmd = New OleDbCommand("SELECT om_list.invoice_no as invoice_no, om_list.internal_id as internal_id, om_list.customer_id as customer_id, om_list.account_name as account_name, om_list.seller_name as seller_name, om_list.terms as terms, om_list.date_invoice as date_invoice, cancel_discount.date_deliver as date_deliver, om_list.segment as segment, sum(cancel_discount.invoice_amnt) as invoice_amnt, sum(app_deduc_rgpi.amount) as rgpi_deduction, sum(app_deduc_mda.amount) as mda_deduction, rgpi_deduction + mda_deduction AS total_deduction, sum(payment_applied.payment_applied) as payment_applied FROM (om_list Inner Join (payment_applied Inner Join cancel_discount on payment_applied.invoice_no = cancel_discount.invoice_no) on om_list.invoice_no = payment_applied.invoice_no) Inner Join (app_deduc_rgpi Inner Join app_deduc_mda on app_deduc_rgpi.invoice_no = app_deduc_mda.invoice_no) on om_list.invoice_no = app_deduc_rgpi.invoice_no group by om_list.invoice_no, om_list.internal_id, om_list.customer_id, om_list.account_name, om_list.seller_name, om_list.segment, om_list.terms, om_list.date_invoice, cancel_discount.date_deliver", con)     
con.Open()
adpt = New OleDbDataAdapter(cmd)        
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
dtset = New DataSet()
adpt.Fill(dtset)
con.Close()

I didn't know if I should post it here or on SQL though I made it in VB so I'm gonna post it here. If it's inapporpriate please transfer it to the proper section

Edited by jared.geli
  • 2 Contributors
  • 3 Replies
  • 251 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by jared.geli

Dani AI

Generated

was right to flag the join logic — the doubling almost always comes from joining multiple child tables that each contain several rows per invoice. When you join those raw child tables together the rows multiply (a cross‑product effect), so an invoice with 2 payment rows and 2 deduction rows can produce 4 joined rows and make SUM(...) look twice (or more) as big.

A safer pattern is: aggregate each child table by invoice first, then join those aggregated results to the invoice (master) row. That keeps each invoice represented once in the final result and avoids multiplication. Example pattern (generic SQL):

SELECT o.invoice_no,
       o.internal_id,
       COALESCE(cd.invoice_amnt,0) AS invoice_amnt,
       COALESCE(pa.payment_applied,0) AS payment_applied,
       COALESCE(r.rgpi_deduction,0) AS rgpi_deduction,
       COALESCE(m.mda_deduction,0) AS mda_deduction
FROM om_list o
LEFT JOIN (
  SELECT invoice_no, SUM(invoice_amnt) AS invoice_amnt, MAX(date_deliver) AS date_deliver
  FROM cancel_discount
  GROUP BY invoice_no
) cd ON cd.invoice_no = o.invoice_no
LEFT JOIN (
  SELECT invoice_no, SUM(payment_applied) AS payment_applied
  FROM payment_applied
  GROUP BY invoice_no
) pa ON pa.invoice_no = o.invoice_no
-- repeat for other deduction tables

Quick debugging steps: (1) Join only one child table at a time to see correct sums. (2) Run SELECT invoice_no, COUNT(*) FROM <child> GROUP BY invoice_no to see how many rows you have per invoice. (3) If values still look off, remove aggregates and inspect the raw joined rows to spot multiplicative combinations. Avoid relying on DISTINCT to mask the problem — it hides the root cause. Finally, prefer LEFT JOIN to keep invoices with no child rows, and use COALESCE/ISNULL as appropriate for your backend to turn NULLs into zeros. This approach is more readable, performant (with indexes), and less error‑prone than joining raw detail tables directly.

Recommended Answers

All 3 Replies

Member Avatar for PerplexedB

Your payment_apply JOIN does not have a filter. I hope for your sake that that is your problem, otherwise you should rethink your logic from scratch.

Member Avatar for jared.geli

I think you're right I don't have one but I have no idea how I'm going to make one

Member Avatar for jared.geli

Problem solved. I used multiple subqueries for computations

Select Distinct(om_list.invoice_no) as invoice_no, om_list.internal_id as internal_id, om_list.customer_id as customer_id, om_list.account_name as account_name, om_list.seller_name as seller_name, om_list.terms as terms, om_list.date_invoice as date_invoice, cancel_discount.date_deliver as date_deliver, om_list.segment as segment, a.invoice_amnt, b.payment_applied, c.rgpi_deduction, d.mda_deduction From (Select sum(invoice_amnt) as invoice_amnt From cancel_discount, om_list where cancel_discount.invoice_no = om_list.invoice_no) as a, (Select sum(payment_applied) as payment_applied From payment_applied, om_list where payment_applied.invoice_no = om_list.invoice_no) as b, (Select sum(amount) as rgpi_deduction From app_deduc_rgpi, om_list where app_deduc_rgpi.invoice_no = om_list.invoice_no) as c, (Select sum(amount) as mda_deduction From app_deduc_mda, om_list where app_deduc_mda.invoice_no = om_list.invoice_no) as d, om_list, cancel_discount

PerplexedB thanks for pointing out about the Join

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.