>I have created 6 tables: Order , Customer, ShipTo, BillTo, and ShipVia and Transport Tables in a database.
>
>I need information in all these files pertaining to a specific order to be displayed on the Order form. Can I get all this information by creating a Local View with on SQL statement and multiple INNER JOINS? I read there is a limit to 4 tables when using an INNER JOIN. Is there a way around this?
>
>Following is the general structure:
>===================================
>
>Order.DBF
>---------
>ID
>PONo
>CustomerID
>
>Customer.DBF
>------------
>ID
>cCode
>cName
>
>ShipTo.DBF
>------------
>ID
>cCustomerID
>cCode
>cName
>
>BillTo.DBF
>------------
>ID
>cCustomerID
>cCode
>cName
>
>ShipVia.DBF
>------------
>ID
>cCustomerID
>cCode
>cTransportID
>
>Transport.DBF
>------------
>ID
>cCode
>cName
>
>ShipTo, BillTo, and ShipVia records belong to the Customers related on ID & CumstomerID. (One to Many)
>
>ShipVia belongs to Customers AND requires a selection from the Transport.dbf (list of all transports)
>
>When creating an order, a customer is selected, then the billto, then the shipto, then the shipvia.
The limit for a JOIN is 10 tables.
Performance is often much better if you join tables 2 at a time (join 2, join the result with a third table, etc.).
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)