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
  • 236 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by jared.geli
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.