Sure.
Nodes table contains 3 fields.
N X Y
3467 172653 298723
2173 172550 298834
2257 172403 298653
The Links table only has 3 fields that Matter.
A B ST_Name
3467 2173 1st St.
2173 2257 2nd St.
2257 3467 3rd St.
The result would look like this:
A B St_Name X1 Y1 X2 Y2 DX DY
3467 2173 1st St. 172653 298723 172550 298834 103 111
2173 2257 2nd St. 172550 298834 172403 298653 147 181
2257 3467 3rd St. 172403 298653 172653 298723 250 70
>Don,
>Can you post some simple data from both tables and desired result?
>
>
>
>>Thanks Sergy and Borislav -
>>I guess my original question did not fully explain what I am trying to do. Sergey's answer gets me close but not quite there. Say there are 2 tables Links and Nodes. Each record of the Links table contains the the node numbers for the endpoints of a line. The Nodes table contains the x,y coordinates for each of the nodes. The task is to select all the records from the Links table and add the x and y coordinates for the 2 ends of the line. Thus X1 and Y1 define the first endpoint and X2 and Y2 define the second endpoint. We also want to compute the deltaX and delta Y for the 2 endpoints so we can tell if the line is mostly horizontal or mostly verticle. Based on Sergey's response I have coded:
>>
>>cd \foxpro\networkupdate
>>close tables all
>>use linkdata in 0 alias links
>>use xydata in 0 alias nodes
>>use xydata in 0 alias nodes2 again
>> >select dt1.*, nodes2.x as x2, nodes2.y as y2, ;
>>abs(dt1.x1-nodes2.x) as dx, abs(dt1.y1-nodes2.y) as dy ;
>>from ( ;
>> select links.*, nodes.n, nodes.x as x1, nodes.y as y1 ;
>> from nodes ;
>> right outer join links ;
>> on nodes.n = links.a ;
>> where st_name <> ' ' ;
>> ) dt1 ;
>>right outer join dt1 ;
>>on dt1.n = nodes2.n ;
>>where dt1.st_name <> ' '
>>
>>Which gets me almost there but not quite because the outside SELECT wants to see dt1 as a preexisting table. Can either of you tell me where to go from here?
>>
>>Thanks
>>
>>
>>
>>>That what we get when assuming anything. :)
>>>Now lets wait and see who's assumption is correct.
>>>
>>>>But I am not referencing X2 and Y2, I am referencing nodes.x and nodes.y.
>>>>If they are calculated fields, you are right, but they are not (if the example SELECT is correct).
>>>>
>>>>>It's not going to work. You cannot reference calculated fields in consequent calculations in the same list. You can use derived table
>>>>>SELECT *, ABS(dt1.x2-dt2.y2) AS dx ;
>>>>> FROM ( ;
>>>>>select temp1.*,nodes.x as x2, nodes.y as y2,
>>>>> from UPDNETATR!xydata nodes ;
>>>>> right outer join temp1 ;
>>>>> on nodes.n = temp1.b;
>>>>> where st_name <> ' ' ;
>>>>> order by temp1.st_name ;
>>>>> ) dt1
>>>>>
>>>>>>Yes it is:
>>>>>>BTW the result contains
x2 and
Y2 :-)
>>>>>>of course if you didn't have X1 in Temp1
>>>>>>
>>>>>>select temp1.*,nodes.x as x2, nodes.y as y2, ABS(nodes.x-nodes.y) AS dx;
>>>>>> from UPDNETATR!xydata nodes ;
>>>>>> right outer join temp1 ;
>>>>>> on nodes.n = temp1.b;
>>>>>> where st_name <> ' ' ;
>>>>>> order by temp1.st_name
>>>>>>
