How come this query doesn't work?

SELECT `Field` FROM ( SHOW COLUMNS FROM Cards ) AS alias

I don't want all the columns that SHOW COLUMNS returns, just `Field`.

lawpoop escribi�:
> How come this query doesn't work?
> 
> SELECT `Field` FROM ( SHOW COLUMNS FROM Cards ) AS alias

Currently, a subquery in MySQL is just "a SELECT statement within 
another statement":

http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

You know, features do not exist by default, someone must implement them 
before they're available ;-P

 >
 > I don't want all the columns that SHOW COLUMNS returns, just `Field`.

You might want to query the COLUMNS table in the INFORMATION_SCHEMA 
database, although it's not necessarily available. A better alternative 
could be using your client side language to parse the result set.


-- 
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Nov 19, 10:41=A0am, "=C1lvaro G. Vicario"
<alvaro.NOSPAMTH...@demogracia.com.invalid> wrote:
> lawpoop escribi=F3:
>
> > How come this query doesn't work?
>
> > SELECT `Field` FROM ( SHOW COLUMNS FROM Cards ) AS alias
>
> Currently, a subquery in MySQL is just "a SELECT statement within
> another statement":
>
> http://dev.mysql.com/doc/refman/5.1/en/subqueries.html
>
> You know, features do not exist by default, someone must implement them
> before they're available ;-P

Well, sometimes you can set up systems where you 'get' features for
free. Say I write a program that multiplies 6 by 8. If I change 6 and
8  to variables, I then have a program can multiply *any* two numbers,
without having to specify entire multiplications tables in the
program. So we would have the feature of multiplying by 9 or 10 or
whatever, all without having to specify multiplication tables of 9 and
10, etc. So I got a whole set of features 'by default', without having
to program them in specifically.

I can use a SELECT from a view, a table, or a join expression -- those
three things behave enough like a table to be able to all be used in a
subselect. When the programmers set about to implement the feature of
SHOW, they could have chosen to have it give a result that was enough
like a table that it, too, could have been used as a resource

Would it have been that much extra work, in addition to just getting
the feature working? I don't know. In my multiplication example,
writing a program that multiplies x by y isn't that much more
difficult than writing a program that multiplies 8 by 6. And if I
write the extra code for inputs, I get a whole lot of functionality
that I don't when I hard-code 6 and 8.

The results of a SHOW query look enough like a table that I don't see
a good reason *in theory* why they couldn't have had it return a
generic table result at the time when they wrote it. :) ( I assume
there were good reasons in practice not to )

On 19 Nov, 19:06, lawpoop <lawp...@gmail.com> wrote:
[...]
> I can use a SELECT from a view, a table, or a join expression -- those
> three things behave enough like a table to be able to all be used in a
> subselect. When the programmers set about to implement the feature of
> SHOW, they could have chosen to have it give a result that was enough
> like a table that it, too, could have been used as a resource

You can get the the information that you need from the information
schema, so I really don't see a problem:

select column_name from information_schema.columns where table_name =
'cards'

[...]

> The results of a SHOW query look enough like a table that I don't see
> a good reason *in theory* why they couldn't have had it return a
> generic table result at the time when they wrote it. :) ( I assume
> there were good reasons in practice not to )

MySql have matured much in version 5 but there are a lot of things
that are still missing IMO (check constraints comes to mind).

But let's assume that your "dream" comes true. People would probably
start using this new feature, i.e. soon enough there would be a bunch
of "sql" around the world containing this non standard feature. The
effect would probably be that much time has to be spent on maintaining
this and other special constructs, instead of developing standard
constructions.

/Lennart

On Nov 19, 1:59=A0pm, Lennart <erik.lennart.jons...@gmail.com> wrote:

> But let's assume that your "dream" comes true. People would probably
> start using this new feature, i.e. soon enough there would be a bunch
> of "sql" around the world containing this non standard feature. The
> effect would probably be that much time has to be spent on maintaining
> this and other special constructs, instead of developing standard
> constructions.

Let me see if I'm hearing you correctly -- the reason this query
doesn't work because it has non-standard SQL in it? That makes sense.

On 19 Nov, 21:25, lawpoop <lawp...@gmail.com> wrote:
> On Nov 19, 1:59=A0pm, Lennart <erik.lennart.jons...@gmail.com> wrote:
>
> > But let's assume that your "dream" comes true. People would probably
> > start using this new feature, i.e. soon enough there would be a bunch
> > of "sql" around the world containing this non standard feature. The
> > effect would probably be that much time has to be spent on maintaining
> > this and other special constructs, instead of developing standard
> > constructions.
>
> Let me see if I'm hearing you correctly -- the reason this query
> doesn't work because it has non-standard SQL in it? That makes sense.

Yes, there is some info regarding this in:

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

Scroll down to the section starting with:

The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more
consistent way to provide access to the information provided by the
various SHOW statements that MySQL supports (SHOW DATABASES, SHOW
TABLES, and so forth).

/Lennart