Victor,
>But I need it in One sql statement. :-(
Why must it be one SQL? Sometimes two SQL statements will give you better performance and/or more readable code.
>I want to select all the names in table a where uCode = 'g'
>And i want the names of the companies they work for (also stored in this table A)
>Al the names in this table can be linked to zero or more other names in this same table A through table B
>So the field name can hold the name of a person and the name of a company.
>If it's a company the field company holds value .t.
>
Assuming TableA contains your names, and TableB provides the junction
You can join a table to itself using as aliases:
select people.name, company.name ;
from tableA as people ;
inner join TableB ;
on people.id = tableB.id ;
left join tableA as company ;
on tableB.id2 = company.id and company.company ;
where people.uCode = 'g'
>I would like to know the (foxpro) sql -syntax for this problem.
>I've managed to do it in two steps by first creating a cursor
>
>select names from A where uCode = 'g' " into cursor crsC
>
>and then use this cursor in the second select statement
>
>select crsC.name as person, A.name as Company from crsC, table A, table B;
>where crsC.id = B.id1
>where B.id2 = A.id
>and A.company = .t.