Posted by NC on 05/20/06 19:41

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

[Back to original message]