Need help with joining 2 tables, Values are not matching
I have Customer table and Invoice table, they are joined on Customer_id. Customer name is also common between these 2 tables.
Users want to filter data on Customer name.
The issue am facing here is if i take customer name from Customer table in the search object i get the count(costumer_id) right but the invoice amount is messed up. And if i take customer name from Invoice table i get the invoice amount right but the count(costumer_id) is messed up.
1.I tried a left join on Invoice table to Customer table as every Invoice must and should have a customer_id. Here i got the count(costumer_id) right but the Invoice amount is almost tripled.
2.Also I tried creating a linked key on Customer_id and Customer_Name. Here both count(costumer_id) and Invoice amount is more than expected
3.Outer join didn't work either, the application size bumped to twice its size.
4.Applymap didn't help much,taking forever to reload.
Can someone please suggest me how to fix this issue asap.
Re: Need help with joining 2 tables, Values are not matching
From your description it sounds like the table relationship needs to look like this (no joins in the scripting required).
As for the user interface calculations, it sounds like your customer to invoice data has a many to many relationship.
Selecting one customer will not reconcile to the amount of a single invoice, but all that customer's potential invoices, if you make a selection of one distinct customer & invoice and the amount doesn't reconcile, then something indeed is wrong.
If you choose a single invoice and get a larger than expected result from Count(CUSTOMER_ID), you need to add DISTINCT to your syntax, as you are probably tabulating all the times the CUSTOMER_ID appears in the INVOICES table.