HELP: Query Advice

[Ian Devaney]

On 2 May 2004, at 6:27 pm, valentina-request at lists.macserve.net wrote:
>>
>>
>> 2 questions:
>>
>> 1, How do I structure the SQL to get all the data?
>
> I think you need to use table aliases
>
>     select
>     from Contacts, Contact_Groups, Lists a, lists b, lists c
>     WHERE ...
>

Oops! Didn't know you could do this! Thanks.


>
>> 2, Once I have a cursor with all the data what is the best way to deal
>> with it?
>
>
>> In Valentina I can't say:
>>
>> "lists.list_value as Prefix, lists.list_value as Suffix,
>> lists.list_value as Country"
>
>> So what do most people do in this situation?
>
> You can use index to get field.
>
>     field(1)

Just wondered if there was a better way. Will this be improved in later 
versions?

>
> Also I wonder:
>     do you have strong reasons to keep in the same column 'Value'
>     data of different types? May be better to use 3 tables ?

You could be right. Just thought it might be tidier. I use a list table 
with key-value pairs:

contactPrefix - Mr.
contactPrefix - Mrs.

country - Ireland
country - UK

etc.

This works really well for most things and is very simple for editing 
etc..

Thanks,

Ian