sql statement ?

[engan]

Dear All,

I am trying to write a select statement with the following situation :


Table1 (Order.dbf) Table2 (Customer.dbf)(For Customer and Agent)
OrderNo C12 IDCust C12
IDCustomer C12 IDName C30
IDAgent C12

I want my sql result to be

OrderNo IDName IDName
OrderNo IDName IDName

where the first column IDName is from IDCustomer and the second column IDName is IDAgent

How do I write the sql statement with the above situation ?

Thanks
Eng An

 

[craigber]

You can't have two columns with the same name.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[Stefan5627]

Try this one:

select A.OrderNo, B.IDName as custname, C.IDName as agentname ;
from Table1 A left outer join Table2 B on B.IDCust == A.IDCustomer ;
			  left outer join table2 C on C.IDCust == A.IDAgent	;
into cursor mycursor

 

[engan]

Craig Bernston,

What I did is I store only ID Customer and ID Agent in order.dbf. Then I want to generate data using sql where the fisrt column is orderid, 2nd column is the name of the customer, then 3rd column is the name of the agent where both name is stored in one file called customer.dbf

So the result of sql query might look like this

Order No. Customer Name Agent Name
000000001 John Peter
000000002 Marry Ann
.
.
etc

Eng An