>>>

>>>create cursor stylelist (style c(10),synonyms m)
>>>select styles
>>>scan
>>>  select synonyms
>>>  lcSynonym = ""
>>>  scan while fk = styles.pk
>>>   lcSynonyms = m.lcSynonyms + iif(empty(m.lcSynonyms),"",",") + synonyms.synonym
>>>  endscan
>>>  if _Tally > 0
>>>    lcSynonyms = "("+m.lcSynonyms+")"
>>>  endif
>>>  select styles
>>>  insert into stylelist values (styles.style,m.lcSynonyms)
>>>endscan
>>>select stylelist
>>>browse
>>>

>>
>>I forgot to mention that the result as to be also with the OleDb provider. Thus, one big SQL to achieve all that.
>
>Michel,
>Saying OleDb Provider and I know you're doing Vb.Net this question is really meant how to do from VB.Net, right? Otherwise doing that with a single SQL (though might be possible with VFP's-I don't know) if ever could be done I think would be slow.
>Hopefully I'm right it's for VB.Net, and then you can convert this one easily:
>

>using System;
>using System.Data;
>using System.Data.OleDb;
>using System.Text;
>
>namespace RelatedDataset
>{
>  class Class1
>  {
>   [STAThread]
>   static void Main()
>   {
>    string strcon,strsql1,strsql2;
>    strcon = @"Provider=VFPOLEDB;Data Source=C:\temp\";
>
>    // Just plain select
>    strsql1 = "select * from Styles";
>    strsql2 = "select * from Synonyms";
>
>    DataSet ds = new DataSet();
>
>    OleDbConnection conn = new OleDbConnection(strcon);
>    conn.Open();
>
>    OleDbDataAdapter daStyles = new OleDbDataAdapter(strsql1, conn);
>    OleDbDataAdapter daSynonyms = new OleDbDataAdapter(strsql2, conn);
>
>    daStyles.Fill(ds,"styles");
>    daSynonyms.Fill(ds,"synonyms");
>    conn.Close();
>
>    // Add a calculated column to Styles
>    ds.Tables["Styles"].Columns.Add("mySynonyms", typeof(string));
>
>    // relate tables: Styles.pk -- synonyms.fk
>    DataRelation myRel = new DataRelation("StyleSynonyms",
>         ds.Tables["styles"].Columns["pk"],
>         ds.Tables["synonyms"].Columns["fk"],
>         false);
>    ds.Relations.Add(myRel);
>
>    // StringBuilder is optimized for string operations
>    StringBuilder sb = new StringBuilder();
>    foreach (DataRow row in ds.Tables["Styles"].Rows)
>    {
>	sb.Length = 0;
>        DataRow[] aRows = row.GetChildRows(myRel);
>        // Collect synonyms for current row
>        if (aRows.Length > 0) {
>	   sb.Append("("); }
>	for (int childs=0;childs<aRows.Length;childs++)
>	{
>                if (childs > 0) {
>                   sb.Append(","); }
>		sb.Append( ((string)aRows[childs]["synonym"]).Trim() );
>	}
>        if (aRows.Length > 0) {
>	   sb.Append(")"); }
>        // add collected to calculated column
>        row["mySynonyms"] = sb.ToString();
>    }
>   /* List from current styles table with added calculated column */
>   foreach(DataRow row in ds.Tables["styles"].Rows)
>   {
>	Console.WriteLine("Style:{0}\tSynonyms:{1}", row["style"],row["mySynonyms"]);
>   }
>  }
> }
>}
>

>
>PS: BTW this is VFP code to create test tables:
>

>local ix,jx
>Create Table ("c:\temp\styles") free (Style c(10), pk i)
>Create Table ("c:\temp\synonyms") (synonym c(10), pk i)
>For ix =1 To 20
>  Insert Into ("c:\temp\styles") Values ('style'+Padl(m.ix,2,'0'),m.ix)
>  For jx=1 To Int(Rand()*5)
>    Insert Into ("c:\temp\synonyms") Values ('synonym'+Padl(m.jx,2,'0'),m.ix)
>  Endfor
>Endfor
>

>Synonyms might be 0-N with no practical limit I think.
>Cetin

Hi,
Did you see my suggestion that Michel just used your previous code in a SP?
Regards,
Viv