2 statements in to 1

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



Can this be done?







Brian

Re: 2 statements in to 1

Brian wrote:
>
> 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...

Cheers,
NC

Re: 2 statements in to 1

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