Using entire query as table name in SQL

Question

How to name an entire select statement query as a table-->

(select col1, col2, col3

from customers..

where col1....) as Table1;

I want to do this for 3 tables which then i keep on using in the same SQL Syntax. The operations I later perform on these tables (Table1, Table2, Table3) are- combining them (TableX) , counting values from Table1 with some filters and displaying as a column next to TableX.

I am able to do this in Microsoft Access, because I can store all three tables, separately manipulate them with filters, and then write individual queries for combining and counting and displaying results as one final table. I am not able to write a single start to end query in SQL. Please help!

hcaelxxam · Accepted Answer · 2015-04-15 02:40:57Z

7

You say that you can do this with Microsoft Access, so I assume you are not trying to use it now.

In T-SQL (SQL Server), you can use WITH

WITH Tab1 AS (
  SELECT
    * 
  FROM
    TabA
),  Tab2 AS (
  SELECT
    * 
  FROM
    TabB
), Tab3 AS (
  SELECT
    * 
  FROM
    TabC
)

SELECT
  *
FROM
  Tab1
INNER JOIN
  Tab2
ON
  1=1
INNER JOIN
  Tab3
ON
  1=1

answered Apr 15, 2015 at 2:40

hcaelxxam

7864 silver badges16 bronze badges

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

Comments

Gord Thompson · Accepted Answer · 2015-04-15 12:13:27Z

1

Yes, you can certainly use aliased subqueries like "tables" in Access SQL:

SELECT q1.DonorID, q1.LastName, SUM(q2.Amount) AS SumOfAmount
FROM
    (
        SELECT DonorID, LastName
        FROM tblDonors
        WHERE LastName LIKE 'Thomp*'
    ) AS q1
    INNER JOIN
    (
        SELECT DonorID, Amount
        FROM tblDonations
        WHERE Amount < 5
    ) AS q2
        ON q1.DonorID = q2.DonorID
GROUP BY q1.DonorID, q1.LastName

answered Apr 15, 2015 at 12:13

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

5 Comments

ASuit Over a year ago

Thanks Gord. This worked. I had few columns in these tables that werent matching. So, I had to put full outer join to connect them.

ASuit Over a year ago

I have a new problem now. After combining 3 tables, I am exporting results in Excel. And on every blank columns I am getting "?" . I need to replace it with blanks in my sql query.

ASuit Over a year ago

I want to use the CASE statement here. CASE WHEN col1 IS NULL THEN [col2] END Something like this. And I have to change this in Table 1. And, display combination of table 1,2,3 in Table X. Please Help!

Gord Thompson Over a year ago

Are these new queries going to be run under Microsoft SQL Server?

ASuit Over a year ago

It was in one of the tables. Anyways. I solved this part. Thank you so much for your help. .

Collectives™ on Stack Overflow

Using entire query as table name in SQL

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related