Hello, I need guidance. I have two tables, trans and detail, simple one to many. For every transaction (trans) there can be many expenses (details). There is a tax field in tans and in detail, in the event that a particular detail has tax and the other details are exempt. Most instances tax is entered one in trans for all of the details.
The problem is that when I query, say for example want to aggregate and analyze all activity on these tables; SELECT detail.expensetype, detail.amount, trans.tax FROM trans INNER JOIN detail on trans.tranid = detail.tranid, I have a tax item for every distinct detail type (since there can be man detail, expense types, for each transaction, or when theres > 1. I'm at a loss, how can I resolve this? I cant think of a solution other than allocating the tax over expense types (tran.tax/SELECT COUNT(detail.ID) FROM detail) or something of the like.
Any thoughts? Thank you in advance.
The problem is that when I query, say for example want to aggregate and analyze all activity on these tables; SELECT detail.expensetype, detail.amount, trans.tax FROM trans INNER JOIN detail on trans.tranid = detail.tranid, I have a tax item for every distinct detail type (since there can be man detail, expense types, for each transaction, or when theres > 1. I'm at a loss, how can I resolve this? I cant think of a solution other than allocating the tax over expense types (tran.tax/SELECT COUNT(detail.ID) FROM detail) or something of the like.
Any thoughts? Thank you in advance.
one to many delimma; variable in one cascading to the many
0 commentaires:
Enregistrer un commentaire