>SQL Select statements are not my strong point, so I could use a little help with this one. Perhaps someone can show me how to code this. Her is the problem:
>
>I have a parent and child table, and for this example I will call the two tables Parent and Child. The 2 tables are related by a field called id_field. The Parent table holds the first name and last name of the Supervisor, and the address of this office location (and there are an unlimited amount of these sales offices). The Child Table holds the First Name, Last Name, etc. of each Salesmen in a local sales office - and there is never more than 20 salesmen in an office, and they are only part of one office.
>
>What I need is a Select statement that will put a row in a cursor one per Sales Office that will also list the First and Last name of each salesmen in that office. As an example: I need it to have fields of:
>
>id_field
>FirstName (from Parent Table - is supervisor)
>LastName (from Parent Table - is supervisor)
>Address, city, State,zip (from Parent Table)
>FirstName1 (from Child Table, is the name of the first salesmen)
>LastName1 (from Child Table, is the name of the first salesmen)
>FirstName2 (from Child Table, is the name of the second salesmen)
>LastName2 (from Child Table, is the name of the second salesmen)
>etc. for 3rd salesman, 4th salesman, etc.
>
>One of the problems is some offices only have 2 or 3 salesmen, but none have more than 20. However, none might have 20 salesmen, but the max any office might have is 9 or 12, etc.
>
>Any help is appreciated. I figured I could write a routine and populate a table to come up with the required results, but I would prefer a Select statement that could do this (as the code would be shorter and quicker, and also I could learn something in the process).
>
>Any help is greatly appreciated.
>
>Mel Cummings

Mel,
Practically I wouldn't try to do this in one SQL.
"...as the code would be shorter and quicker" statement is not right IMHO. You might be surprised multiple SQL and/or xbase commands might perform much better.
Many highly optimized code are tricky but this one is more understandable and easy to implement IMHO (if you testdrive it vs SQL solutions I'd like to know the result - with this code as is it takes 0.11 seconds on my system : AthlonXP 2500+,512Mb,64Mb is used by on board VGA. Still room to optimize it sounds)

#Define OFFICESCOUNT 1000
#Define MAXSALESMAN 20

* Create test cursors
Create Cursor parentTab ;
    (officeID i, ;
    FirstName c(15),;
    LastName c(15),;
    Address c(15), ;
    city c(15), ;
    State c(15),;
    zip c(15))

Create Cursor childTab ;
    (officeID i, FirstName c(15), LastName c(15))

Local ix,jx
Rand(-1)
For ix=1 To OFFICESCOUNT
    Insert Into parentTab Values ;
        (m.ix, ;
        'SVFName_'+Padl(m.ix,5,'0'),;
        'SVLName_'+Padl(m.ix,5,'0'),;
        'Addr_'+Padl(m.ix,5,'0'),;
        'City_'+Padl(m.ix,5,'0'),;
        'State_'+Padl(m.ix,5,'0'),;
        'Zip_'+Padl(m.ix,5,'0'))
    For jx=1 To Ceiling(Rand()*MAXSALESMAN)
        Insert Into childTab Values ;
            (m.ix, 'Office_'+Padl(m.ix,5,'0'), 'LName_'+Padl(m.jx,5,'0'))
    Endfor
Endfor
* It's likely there would be indexes on officeID so let's create
Select parentTab
Index On officeID Tag officeID
Set Order To 0
Select childTab
Index On officeID Tag officeID
Set Order To 0
* Create test cursors

lnSeconds = Seconds()
* Find out how many xtra columns we need
Local Array arrXtraFields[1], arrStruc[1]
Local lnXtra
Select Cnt(*) ;
    from childTab ;
    order By 1 Desc ;
    group By officeID ;
    into Array arrXtraFields

lnXtra = arrXtraFields
*Get regular fields from parent
Select parentTab
Afields(arrStruc)
AdelCols(@arrStruc,5,16) && We simply don't need cols after 4

#Define XTRACOLS 2
Dimension arrStruc[Alen(arrStruc,1)+m.lnXtra*XTRACOLS,4] && Add new rows to structure array

For ix = 1 To m.lnXtra && Add new cols
    arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+1,1] = 'SFn_'+Padl(m.ix,5,'0')
    arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+2,1] = 'SLn_'+Padl(m.ix,5,'0')
    Store 'C' To ;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+1,2],;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+2,2]

    Store 15 To ;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+1,3],;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+2,3]

    Store 0 To ;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+1,4],;
        arrStruc[Fcount('parentTab')+(m.ix-1)*XTRACOLS+2,4]
Endfor
Create Cursor mySelect From Array arrStruc && New cursor is ready to populate
* New cursor might be populated in many ways
* This is just one of them

Local Array arrP[Fcount('parentTab')], ;
    arrC[m.lnXtra*XTRACOLS],;
    arrRec[1,Fcount('mySelect')]
Local lnChildren
Select childTab
Set Order To officeID
Select parentTab
Scan
    arrRec = ''
    arrC   = ''
    lnChildren = 0
    Scatter To arrP
    If Seek(parentTab.officeID, 'childTab', 'OfficeID')
        Select childTab
        Scan While officeID = parentTab.officeID
            arrC[m.lnChildren*XTRACOLS+1] = childTab.FirstName
            arrC[m.lnChildren*XTRACOLS+2] = childTab.LastName
            lnChildren = lnChildren + 1
        Endscan
    Endif
    Acopy(arrP,arrRec,1,-1,1)
    Acopy(arrC,arrRec,1,-1,Alen(arrP)+1)
    Insert Into mySelect From Array arrRec
Endscan
Select mySelect
Locate
? Seconds()-m.lnSeconds
Browse

Function AdelRows
    Lparameters taArray, tnStartRow, tnEndRow
    Local ix
    For ix=m.tnEndRow To m.tnStartRow Step -1
        Adel(taArray,m.ix)
    Endfor
    Dimension taArray[Alen(taArray,1)-(m.tnEndRow-m.tnStartRow+1),Alen(taArray,2)]

Function AdelCols
    Lparameters taArray, tnStartCol, tnEndCol
    Atranspose(@taArray)
    AdelRows(@taArray, m.tnStartCol, m.tnEndCol)
    Atranspose(@taArray)

Function Atranspose
    Lparameters taArray
    Local ix,jx
    Local Array aConversion[Alen(taArray,2),Alen(taArray,1)]
    For ix=1 To Alen(taArray,1)
        For jx=1 To Alen(taArray,2)
            aConversion[m.jx,m.ix] = taArray[m.ix,m.jx]
        Endfor
    Endfor
    Dimension taArray[Alen(taArray,2),Alen(taArray,1)]
    Acopy(aConversion,taArray)

Cetin