implementing MANY to MANY relation

Hi everybody
we have a many-many relation between 2 tables , prepared by a third table and there is child table for one of them as :
My tables are : invhdr , invitems , vhcltrans and invvhcls
invitems is the child for invhdr
vhcltrans : contains the vehicles delivery transactions
so each invoice may be delivered by 1 or mare vehicles , also 1 vehicle may deliver 1 or more invoice items , so we use the table invvhcls to keep the records for invoice(s) vehicle(s) ( many-many )
What we need is how to display for each invoice it's items together with it's vehicles >

Thanks
Sami Musleh

invvhcls is your fact table. The rest should be straightforward.

Thank you for your reply
Actually our fact table is the line items (the granularity),furthermore there are no relation between the invitems and invvhcls , so the returned records from the query contains each record from the invitems withe each record from the invvhcls
for example :
the invitems contains the following lines for an invoice:
1-Banana
2-Apple
3-Orange

and this invoice was delivered vi 2 vehicles :
1-Vcl-333
2-Vcl-555

there are no information about which vehicle loaded with Banana or Orange or Apple

so the result of the query will be
Banana - Vcl-333
Banana - Vcl-555
Apple - Vcl-333
Apple - Vcl-555
Orange - Vcl-333
Orange - Vcl-555

Hi Sami,

I think you need make the grain of your invvhcls more detailed, adding in the invoice lines.

Good luck!
Mike

samimusleh wrote:
there are no information about which vehicle loaded with Banana or Orange or Apple

What table has that information?

If you want to be able to report what items were delivered on which truck, you need to capture that information. You don't have it. There is nothing you can do from a modeling perspective to invent information you don't have.