CODEWITHSUNDEEP
mysqlsql
user1588670
user1588670

Reputation: 283

MySQL query within a query

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

Upvotes: 0

Views: 1529

Answers (2)

Roland Bouman
Roland Bouman

Reputation: 31961

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

Upvotes: -1

alanmanderson
alanmanderson

Reputation: 8200

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?

Upvotes: 1

Related Questions