[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

outer join 2 fields vfp9

Posted on 2010-09-08
5
Medium Priority
?
374 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:ClaytonGlass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:z_alex
ID: 33626648
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
0
 

Author Comment

by:ClaytonGlass
ID: 33626940
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
0
 
LVL 43

Accepted Solution

by:
pcelba earned 500 total points
ID: 33627015
Next time please don't write "But vfp returns an error; it cannot seem to define the second join."

To post the query and the error message text is more readable (at least for me)

If the combination of order_no and line is unique in your tables then you may use following query:


SELECT o.*, l.* ;
  FROM orders o ;
  LEFT OUTER JOIN leads l ON o.order_no = l.order_no AND o.line = l.line

Open in new window

0
 
LVL 43

Expert Comment

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

Author Closing Comment

by:ClaytonGlass
ID: 33627141
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question