thanks for responding and sorry - ran out of time to post so the previous message is incomplete.
When you say cartesian, do you mean all permutations between tables?
In any case, conceptually, if all you want to do is retrieve records from table1 with all the foreign keys replaced with associated descriptive names contained in other ancillary tables. What would be the proper method to do it?
For instance, if a workorder table contains the following records:
WORKORDER Table cuid: int // foreign int key to customer table names.
stid: int // foreign int key to state table names.
When i retrieve the workorder records for display, i want the select result list fields to contain the descriptive names associated with the foreign keys like the following:
SELECT customer.name, state.name
FROM workorder, customer, state
WHERE (workorder.cuid = customer.cuid) and (workorder.stid = state.stid)
SELECT customer.name, state.name
FROM workorder
INNER JOIN customer, state
ON (workorder.cuid = customer.cuid) and (workorder.stid = state.stid)
although i think that the following is the better join syntax...
SELECT customer.name, state.name
FROM workorder
JOIN customer ON workorder.cuid = customer.cuid
JOIN state ON workorder.stid = state.stid
The above selects should give me the same results. One result for each record in the workorder table with the foreign keys replaced with the descriptive names contained in the ancillary name tables.
The following should give me the similar results. One result for each record in the left workorder table with the foreign keys replaced with the descriptive names contained in the ancillary name tables, if possible, even if there are no matches in the right tables.
SELECT customer.name, state.name
FROM workorder
LEFT JOIN customer ON workorder.cuid = customer.cuid
LEFT JOIN state ON workorder.stid = state.stid
Do i have the right method for what i want to do?
