Hi, Victor-

>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.
>
>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.
>
>But I need it in One sql statement. :-(

This is easy if you work with a normalized data structure.

CLOSE ALL
SET SAFETY OFF

CREATE TABLE Companies (iCompanyID I, cCompany C(30))
CREATE TABLE People (iPeopleID I, cName C(30))
CREATE TABLE PeopleXCompanies (iID I, iPeopleID I, iCompanyID I)

INSERT INTO Companies VALUES (1, "Microsoft")
INSERT INTO Companies VALUES (2, "Acme")
INSERT INTO Companies VALUES (3, "ScanSource")

INSERT INTO People VALUES (1, "Sue")
INSERT INTO People VALUES (2, "Frank")
INSERT INTO People VALUES (3, "Al")
INSERT INTO People VALUES (4, "Bob")
INSERT INTO People VALUES (5, "Terry")
INSERT INTO People VALUES (6, "Hank")

INSERT INTO PeopleXCompanies VALUES (1,1,1)
INSERT INTO PeopleXCompanies VALUES (2,1,3)
INSERT INTO PeopleXCompanies VALUES (3,3,1)
INSERT INTO PeopleXCompanies VALUES (4,3,2)
INSERT INTO PeopleXCompanies VALUES (5,3,3)
INSERT INTO PeopleXCompanies VALUES (6,4,3)
INSERT INTO PeopleXCompanies VALUES (7,4,2)
INSERT INTO PeopleXCompanies VALUES (8,5,3)
INSERT INTO PeopleXCompanies VALUES (9,6,1)

SELECT cCompany Company, cName NAME ;
  FROM PeopleXCompanies ;
  INNER JOIN Companies ON Companies.iCompanyID = PeopleXCompanies.iCompanyID ;
  INNER JOIN People ON People.iPeopleID = PeopleXCompanies.iPeopleID INTO CURSOR temp

BROWSE NOWAIT