General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Multiple JOINs SQL/ Local View
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.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only