asked on

outer join 2 fields vfp9

Hi:
I may be missing something here but....
I have 2 vfp tables; orders.dbf and leads.dbf
orders is a table with all sales orders for glass windows in it, including fields 'order_ no' and 'line'
leads is a table with all orders with lead designs, including fields 'order_ no' and 'line'
Most orders have more than one line
Not all orders have lead designs.
So I want to run a query that lists ALL orders, and the lead designs for those orders that have them.
This will require an OUTER JOIN on 'order_no' AND line.
But vfp returns an error; it cannot seem to define the second join.
Am I breaching SQL rules or am I missing something?
Neither table has a key field - they are free tables.
Thanks!

z_alex

Please give sample data
try to use left join also.
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

try this link http://www.myupshare.com/?p=52
or
http://www.w3schools.com/sql/sql_join_left.asp

Richard Teasdale

ASKER

Ok: here are the 2 tables in excel format.
qpos is the table of all sales
leadwork is the table of orders with leadwork.
I need a result table that comprises all of qpos, and bea_nr from leadwork. Joined on auf_nr and auf_pos
Thanks!
qpos.xls
leadwork.xls

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

Looking at your excel sheets if you update the query by actual table and column names then it should work.

Richard Teasdale

ASKER

Thanks, pcelba.
Apologies for the confused message - using the visual designer the message was ".dbf does not exist.". Keying in the SQL as suggested didthe trick!
and thanks to z_alex, too.