>>>>

>>>>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

Now I did:) I thought of that too but creating stored procedures for each similar task sounded to be an overkill. He could decide which path to take:)
Cetin