CODEWITHSUNDEEP
sql-serverdatabase
Jack Kada
Jack Kada

Reputation: 25262

Can a query be used in place of a table in SQL Server

This should really be allowed - I do not understand why it is not.

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
)

Upvotes: 2

Views: 137

Answers (4)

Rob
Rob

Reputation: 525

You are missing an 'alias' on the sub-query (I added an alias 'X' ) 

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
) X

Upvotes: 4

Andrew
Andrew

Reputation: 27314

In SQL Server it is allowed, but the inner select has to be given a name, such as:

SELECT *  
FROM ( 
    SELECT *  
    FROM MyTable
) m

When a name is not supplied it will throw an incorrect syntax error near ')' message.

Upvotes: 10

user121301
user121301

Reputation:

If you add a table alias it should work:

SELECT * 
FROM (
    SELECT * 
    FROM MyTable
) as A

Upvotes: 6

Chris McCall
Chris McCall

Reputation: 10407

There are at least two ways to accomplish this, but what you might be looking for is a Common Table Expression (CTE), introduced in SQL Server 2005.

From the above link:

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Alternately, you can create a View, which is a permanent table-shaped representation of a query that you can access by name:

USE AdventureWorks ;
GO
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS 
SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate
FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ;
GO
SELECT * FROM hiredate_view

Upvotes: 2

Related Questions