Posted by Brian on 06/15/81 11:48

>> I'm not sure if this can be done, but I would like to turn
>> 2 statements in to 1
>>
>> The 1st one returns 2 fields "Bank" and "Advertmode"
>>
>> SELECT bank1, advertmode1 FROM conf WHERE (id = "XYZ")
>>
>> The 2nd one uses the "Bank" field to know what to get and the
>> "Advertmode"
>> is used to set a flag
>>
>> SELECT * FROM profiles WHERE (customer = "TheCustomers")
>> AND (profile_name1 = "Bank1")
>>
>> I need to end up with 4 fields that I can use
>>
>> filename1 (from profiles table)
>> description1 (from profiles table)
>> supplier1 (from profiles table)
>> advertmode1 (from conf table)
>
> This might help:
>
> SELECT conf.bank1 AS bank1,
> conf.advertmode1 AS advertmode1,
> profiles.filename1 AS filename1,
> profiles.description1 AS description1
> FROM conf LEFT JOIN profiles
> ON conf.bank1 = profiles.profile_name1
> WHERE customer = "TheCustomers";
>
> For best performance, all fields mentioned in JOIN and WHERE clauses
> shuld be indexed.
>
>> This statement will be run 14 times to gather the fields
>> 1 to 14 (eg filename1, filename2, filename3 etc) so I am
>> trying to make it quicker to run
>
> It seems to me your data architecture could use some improvement...


Hi NC

Thanks for your help it's all working now, I think I confused you of
something
which was my fault, the whole statement is being run once, but the statement
is
being built up with 14 x advertmode1, filename1, description1,
.....advertmode14, filename14, description14,


Brian

[Back to original message]