>I have a need to a fancy SQL on a child table. I have a Style.dbf table. This one contains 76 records. So, when I select all records, I have 76 records. But, on some of those records, they have a relation into StyleSynonyme.dbf, which acts as a synonym table, when a style could be known under one or additional names. So, basically, on all those 76 style records, only some of them would have one or multiple entries in StyleSynonyme.dbf. When they have a relationship into that table, it could be only one record (assuming one synonym is present) or multipe in situations where the style is known under various names.
>
>So, as you would have guessed. I am looking for a SQL that would gather the required data. Presently, I am doing one SQL to get the 76 records and, when doing the list, I have to do one SQL for each style to see if I have synonyms.
>
>The goal here is to obtain 76 records. Because the list should list 76 styles. But, on those records, I need to gather the information about synonyms, when applicable.
>
>It could be as simple as adding a field in the SQL to collect the 76 styles, which would collect the synonym(s) data, when applicable, such as "Synonym1, Synonym2", for example, where two synonyms would be related to the style record. The list should be as follow:
>
>
>Style1
>Style2 (Synonym)
>Style3
>Style4 (Synonym1, Synonym2)
>Style5
>Style6
>Style7
>Style8
>Style9 (Synonym)
>Style10 (Synonym1, Synonym2)
>...
>
>
>Anyone would know if there would be a faster way to achieve that? Because, presently, going with the OleDb provider, which is extremely slow as oppose to VFP native data engine, doing one additional SQL for each of the 76 styles, is a little bit too much.
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
browseCetin
