MySQL query within a query

Question

So I am trying to write a query where I get the column information from a query this is what i tried:

Select login from TableName AS alias_name

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT,
FROM  INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_Name = 'alias_name' ORDER BY ORDINAL_POSITION ASC;

... this doesn't work because the query is not saved as a temporary table or what not ...

so I need the functionally of the two queries above combined into one query thanks

Please give examples of the output that you want. Your first query returns one column, your second multiple columns. SQL does not support tables that have different numbers of columns on different rows. — Gordon Linoff
– Gordon Linoff, Commented Sep 10, 2012 at 20:12
I need to query to return a list of column names with there data_type char_max_length is_null if the syntax above was valid it would work, if I could create a temporary table that would solve the problem — user1588670
– user1588670, Commented Sep 10, 2012 at 20:26

alanmanderson · Accepted Answer · 2012-09-10 20:13:09Z

1

Try this:

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT, 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_Name IN (SELECT login from TableName) 
ORDER BY ORDINAL_POSITION ASC;

Is that what you are trying to do?

answered Sep 10, 2012 at 20:13

alanmanderson

8,31012 gold badges56 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1588670 Over a year ago

That looks right, but i need it so that the In table can be any query like select * from user

user1588670 Over a year ago

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE EXISTS (SELECT Login from TABLENAME) ORDER BY ORDINAL_POSITION ASC; i tired this but the colum column_name has wy to much data in it, i just need it to be fill with the column names but this is the closest attempt yet

alanmanderson Over a year ago

Something like: SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT, FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN TableName ON TableName.login=INFORMATION_SCHEMA.COLUMNS.TABLE_Name ORDER BY ORDINAL_POSITION ASC;

Roland Bouman · Accepted Answer · 2012-09-10 20:54:47Z

-1

This is not possible; not in pure SQL. This is not a property of MySQL - other RDBMS-es also do not offer query metadata in this way.

The closest you can get to solving this in pure SQL is to turn your query into a view, and then get the info for that view:

CREATE OR REPLACE VIEW _query
AS 
SELECT login 
FROM   TableName 
;

SELECT *
FROM   information_schema.COLUMNS
WHERE  table_schema = SCHEMA()
AND    table_name   = '_query'
;

The problem with this approach is that view names have to be unique, so an application with multiple concurrent users has the challenge to come up with a unique name for the view. This is possible using dynamic sql:

SET @name = CONCAT(_query, connection_id())
,   @stmt = CONCAT(
        ' CREATE VIEW '
    ,   @name
    ,   ' AS SELECT login FROM Tablename'
    )
; 

PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT *
FROM   information_schema.columns
WHERE  table_schema = schema()
AND    table_name = @name
;

Finally, you should probably clean up your view afterwards:

SET @stmt = CONCAT('DROP VIEW ', @name); 
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The question is, why do you need this inside SQL? Typically, resultset metadata is available in SQL clients - typically the driver or client library provides this as you prepare a SQL statement. For instance in JDBC http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getMetaData()

edited Sep 10, 2012 at 20:54

answered Sep 10, 2012 at 20:15

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

7 Comments

user1588670 Over a year ago

there must be a creative way to do this, I don't know if I need a join statement or what

Roland Bouman Over a year ago

Look, MySQL has information_schema, which gives you information about tables, views, etc - Schema object. There is nothing for a query.

Roland Bouman Over a year ago

What you can do, is create a view out of your query, and then ask information_schema.columns. But there is no way to do it for a query. PS, whomever gave me the -1, you're wrong :)

user1588670 Over a year ago

how would I create a view, just saying it is not possible is not very helpful, show me a creative way of doing this as long as I can write queries to accomplish this goal it should work and this should be possible using sql

Roland Bouman Over a year ago

@user1588670 Ok. I'll update my answer, but really - the MySQL manual is quite clear about both creating views and the information_schema.

|

Collectives™ on Stack Overflow

MySQL query within a query

2 Answers 2

3 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related