none
Subquery in a FROM Clause

    Question

  • Hi Guys,

     

    It is possible to put a subquery in the FROM clause? I mean like this :

    SELECT * FROM (SELECT column FROM table where column = @column) WHERE ID = @ID.

     

    Just asking guys coz I think that query might help me a lot.

     

    Thanks in advance..

     

    Sincerely,

     

    Charlie

    Saturday, January 29, 2011 3:24 AM

Answers

  • I think I don't have post here my table structure. My question is simple as what you see above.

    Your question is not obvious and that is why Sylvain asked for the additional information.  Keep in mind that we are trying to help you.  You know more about the problem than we do so providing us with more information will help us provide the best answer to your question sooner. 

    To explain further, in FROM clause subquery I just want to get data from a column to be used as the target table to the query. Why? because I made a table that contains the table names in my database. I need to be dynamic in my codes in case of changes in the future and that is my technique. We are different in our on ways and that is my way.  

    I think I understand better now.  You want a variable table name, returned from a query, to be used in the main query.  So you want:

    SELECT *
    FROM (SELECT TableName FROM dbo.TableList WHERE TableID = @TableID) WHERE ID = @ID;
    

    to behave as:

    SELECT *
    FROM dbo.ActualTableName WHERE ID = @ID;
    

    This will require the use of dynamic SQL rather than a static query.  You'll need to do something like:

    DECLARE 
    	@ID int
    	,@TableID int
    	,@SqlQuery nvarchar(MAX);
    SET @TableID = 1;
    SET @ID = 2;
    
    SELECT @SqlQuery = 'SELECT * FROM '
    	+ (SELECT TableName
    	  FROM dbo.TableList WHERE TableID = @TableID)
    	  + ' WHERE ID = @ID';
      
    EXECUTE sp_executesql
    	@SqlQuery
    	,N'@ID int'
    	,@ID = @ID;

    Personally, I suggest you rethink your approach as dynamic SQL introduces a bit of complexity and issues.  See Erland's thorough article on the subject:  http://www.sommarskog.se/dynamic_sql.html.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Saturday, January 29, 2011 3:54 PM code did not format properly
    • Proposed as answer by Hasham NiazEditor Sunday, January 30, 2011 2:40 AM
    • Marked as answer by Ai-hua Qiu Friday, February 04, 2011 9:38 AM
    Saturday, January 29, 2011 3:52 PM

All replies

  • Charlie

     

    You can do this

     

    SELECT *
    
    FROM (SELECT column FROM table where column = @column) i
    WHERE ID = @ID.
    
    Try this

    Saturday, January 29, 2011 3:34 AM
  • Hi SPRR,

     

    Thanks for the response. You just added i after subquery. The SQL Server will automatically assign Expr in case you didn't. Still, it says 'invalid column_name 'columnname'. How can we achive this? Please help guys...

    Saturday, January 29, 2011 3:44 AM
  • > The SQL Server will automatically assign Expr in case you didn't.

    The graphical query builder (or whatever its name) might add it when you use it to build a query; however, using the graphical builder is not the best thing to do as it cannot write complex queries so you are better of to write your own queries and furthermore, the SQL-Server itself won't add it automatically and if you forget it, it will flag it as an invalid syntax.

    For your specific error, without seeing the exact query that you have wrote nor the design of the tables used in i; it's impossible to tell you what's wrong with it.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Saturday, January 29, 2011 5:38 AM
  • Hi Sylvain,

     

    I think I don't have post here my table structure. My question is simple as what you see above. To explain further, in FROM clause subquery I just want to get data from a column to be used as the target table to the query. Why? because I made a table that contains the table names in my database. I need to be dynamic in my codes in case of changes in the future and that is my technique. We are different in our on ways and that is my way.

     

    Back to my question. I'm just asking if the query is possible, if not so be it. I already googled it and until now no answer found.

    Saturday, January 29, 2011 5:56 AM
  • In place of TableName in From Clause, you can specify the query.... But when you are specifying query, you need to
    put that query in brackets and needs to give alias name to that table.....
    select * from (select * from TableName)t where SomeColumn = 10
    Error you are receiving "Invalid column Name blah blah.." means, you need to select that column also in inner query.
    select * from (select I,J from TableName)t where J = 10
    suppose, in above query, if you didn't select "J" in inner query, you can't use "J" in outer part..
    Saturday, January 29, 2011 6:17 AM
  • So yes it is possible, just put a alias at the end of the subquery and boom it works!

     

    SELECT * FROM (SELECT column FROM table where column = @column) A WHERE ID = @ID.
    

     

    However for Sylvain for this a subquery is useless.  You can have the same result with the followind:


    SELECT [column]  FROM  Table  WHERE ID = @ID And [column] = @column  
    
    It is easier to read and more efficient on large tables.

    Saturday, January 29, 2011 12:55 PM
  • I think I don't have post here my table structure. My question is simple as what you see above.

    Your question is not obvious and that is why Sylvain asked for the additional information.  Keep in mind that we are trying to help you.  You know more about the problem than we do so providing us with more information will help us provide the best answer to your question sooner. 

    To explain further, in FROM clause subquery I just want to get data from a column to be used as the target table to the query. Why? because I made a table that contains the table names in my database. I need to be dynamic in my codes in case of changes in the future and that is my technique. We are different in our on ways and that is my way.  

    I think I understand better now.  You want a variable table name, returned from a query, to be used in the main query.  So you want:

    SELECT *
    FROM (SELECT TableName FROM dbo.TableList WHERE TableID = @TableID) WHERE ID = @ID;
    

    to behave as:

    SELECT *
    FROM dbo.ActualTableName WHERE ID = @ID;
    

    This will require the use of dynamic SQL rather than a static query.  You'll need to do something like:

    DECLARE 
    	@ID int
    	,@TableID int
    	,@SqlQuery nvarchar(MAX);
    SET @TableID = 1;
    SET @ID = 2;
    
    SELECT @SqlQuery = 'SELECT * FROM '
    	+ (SELECT TableName
    	  FROM dbo.TableList WHERE TableID = @TableID)
    	  + ' WHERE ID = @ID';
      
    EXECUTE sp_executesql
    	@SqlQuery
    	,N'@ID int'
    	,@ID = @ID;

    Personally, I suggest you rethink your approach as dynamic SQL introduces a bit of complexity and issues.  See Erland's thorough article on the subject:  http://www.sommarskog.se/dynamic_sql.html.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Saturday, January 29, 2011 3:54 PM code did not format properly
    • Proposed as answer by Hasham NiazEditor Sunday, January 30, 2011 2:40 AM
    • Marked as answer by Ai-hua Qiu Friday, February 04, 2011 9:38 AM
    Saturday, January 29, 2011 3:52 PM
  • Hi Dan,

     

    Thanks for that and to you guys who responded, I really appreciate it. Dan, how about if I will omit the WHERE clause and @TableID? so it will get multiple values in a column. Is it possible?

    Monday, January 31, 2011 2:06 AM
  • A subquery in this context may only return one value or you will get an error.  There may be other ways to achieve the desired result, though.  Exactly what are you trying to accomplish? 

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, January 31, 2011 3:24 AM
  • Hi Sir Dan,

     

    Yes you're right. Maybe it's impossible to achieve the desired output with just a query alone. I instead used my option B, which will result the same output but it's kinda long method. At least, now I know. Thank again Sir Dan...

     

    I love MSDN...

     

    Sincerely,

    Charlie

    Monday, January 31, 2011 3:46 AM
  • I love MSDN...


    +1

    :-)

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, January 31, 2011 12:56 PM