multiple aliases

--_000_0385417EB350D84BAE0D5103C372C65E10B987itsnt426iowauio wa_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a query with three subselects, all referencing the same table. I'd =
like to be able to combine them into one with aliases.

Here's what I have now:

select letter_codename,
(select greek from letter_otherlanguages where letter =3D'A') as greek,
(select french from letter_otherlanguages where letter =3D'A') as french,
(select german from letter_otherlanguages where letter =3D'A') as german
from intl_codes where letter=3D'A';

I'd like to replace it with:

select letter_codename,
(select greek, french, german from letter_otherlanguages where letter =3D'A=
') as (greek, french, german)
from intl_codes where letter=3D'A';

Don't get hung up on the tables and structures, this is just a simple examp=
le. :) I want to use the three subselects because if I use a left join, t=
he processing time goes from .4 to 5 seconds.

Is this possible?

Thanks!


--_000_0385417EB350D84BAE0D5103C372C65E10B987itsnt426iowauio wa_--

RE: multiple aliases

I don't believe it's possible to do what you're suggesting. At least,
according to the second example on this page:
http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.html.


-Travis


-----Original Message-----
From: Ramsey, Robert L [mailto:robert-ramsey@uiowa.edu]
Sent: Monday, September 27, 2010 7:10 AM
To: [MySQL]
Subject: multiple aliases

I have a query with three subselects, all referencing the same table. I'd
like to be able to combine them into one with aliases.

Here's what I have now:

select letter_codename,
(select greek from letter_otherlanguages where letter ='A') as greek,
(select french from letter_otherlanguages where letter ='A') as french,
(select german from letter_otherlanguages where letter ='A') as german
from intl_codes where letter='A';

I'd like to replace it with:

select letter_codename,
(select greek, french, german from letter_otherlanguages where letter ='A')
as (greek, french, german)
from intl_codes where letter='A';

Don't get hung up on the tables and structures, this is just a simple
example. :) I want to use the three subselects because if I use a left
join, the processing time goes from .4 to 5 seconds.

Is this possible?

Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: multiple aliases

On 9/27/2010 9:10 AM, Ramsey, Robert L wrote:
> I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases.
>
> Here's what I have now:
>
> select letter_codename,
> (select greek from letter_otherlanguages where letter ='A') as greek,
> (select french from letter_otherlanguages where letter ='A') as french,
> (select german from letter_otherlanguages where letter ='A') as german
> from intl_codes where letter='A';
>
> I'd like to replace it with:
>
> select letter_codename,
> (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german)
> from intl_codes where letter='A';
>
> Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds.
>
> Is this possible?
>
> Thanks!
>
>

This should work -

SELECT ic.letter_codename, lo.greek greek, lo.french french, lo.german
german from intl_codes ic LEFT JOIN letter_otherlanguages lo on
lo.letter = ic.letter WHERE ic.letter='A';

There should also be an index on both tables where `letter` is the
leftmost element.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org