Subject SV: [firebird-support] Re: SQL 'DeNormalization' approaches
Author Svein Erling Tysvær
>> WITH RECURSIVE AddressOrder(ID, Address, MyOrder) AS

>Vim tells me there is a closing parenthesis missing from this
>statement. :-)

Vim's right, Michael, I admit I was pretty tired when I wrote the answer and still didn't check the syntax.

>> SELECT N.PersonID, N.FirstName, N.LastName, AO1.Address as Add1,
>> AO2.Address as Add2, AO3.Address as Add3
>If the number of addresses and phone numbers is not known, this approach
>won't work. I think SQL won't work at all here. I'd say the requirement
>is wrong.

Yes, you need to know approximately the maximum number of repetitions for this to work. Either that, our specify for your scientist that he gets the first five or ten phone numbers and that you ignore the rest. Sometimes, that is good enough. I've done something similar with parents and children. A person can have lots of children. Since it was a once time requirement, I did obtain the data on all children, but if I'd said that I'd return data for the first 10 children only it might have been OK for the scientist. After all, children number 11 and later hardly produces a statistically significant contribution to most analysis of parenthood (possibly excepting things like examining births from women over 40 years of age) when you examine many thousand parents (at least, in my country it is rare to have more than 10 children).

>You could concatenate all the address and phone records in a parseable
>string representation, but it feels wrong as well.

Maybe scientists could use this if the separator between each record in the list was a tabulator (I've never tried so I don't know if it is possible). I think I'd normally prefer a cutoff point like mentioned above.