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!

Solution 1

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

Solution 2

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

Comments

[ASuit]

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]

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]

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]

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

[ASuit]

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