SQL How do I name tables to refer to them later?

Question

I'd like to create named tables that are transformations of existing tables. I can then join them easily with each other later in my query. I know you can name tables using with [Table Name] as (...), but I then immediately need to select from this table, and I can't refer to it later. How do I make a callable table?

Here's a skeleton of what I'd like

With Table1 as (select...)

With Table2 as (select...)

select * from Table1 join Table2 using Table1.ID = Table2.ID

Here's what I have currently, which doesn't recognise Chart_Titles_Table the second time I try to from it.

with Control_Limit_Table as (
select CHRT_CONTROLLIMITS.CHRTID,CLSTARTDATE,LOWERCONTROLLIMIT,UPPERCONTROLLIMIT,CENTRALLINE from CHRT_CONTROLLIMITS
inner join (
select CHRTID, Max(CLSTARTDATE) as RecentCL from CHRT_CONTROLLIMITS group by CHRTID
) Newest on Newest.CHRTID = CHRT_CONTROLLIMITS.CHRTID and Newest.RecentCL = CLSTARTDATE
)
select * from Control_Limit_Table
;

with Chart_Titles_Table as (
select CHRTID, TITLE, ENGOWNER from CHRT
)

select * from Chart_Titles_Table join
Control_Limit_Table on Control_Limit_Table.chrtid = Chart_Titles_Table.CHRTID

You can use a table variable, a temporary table, a view or an actual table. — HoneyBadger, Jun 21 '21 at 09:28
Hi, take a look at this question --> https://stackoverflow.com/q/5375634/12405227 You can define several tables, but once you you perform the select they go out of scope. If you need them afterwards you should fill a temporary table or create a view as HonneyBadger indicated above. — David, Jun 21 '21 at 09:51

score 3 · Accepted Answer · answered Jun 21 '21 at 10:05

You can use a CTE (declare using WITH) only within a single query, however you can add multiple CTEs to the same query.

For example:

with Control_Limit_Table as (
    select
        CHRT_CONTROLLIMITS.CHRTID,
        CLSTARTDATE,
        LOWERCONTROLLIMIT,
        UPPERCONTROLLIMIT,
        CENTRALLINE
    from CHRT_CONTROLLIMITS
    inner join (
        select
            CHRTID,
            Max(CLSTARTDATE) as RecentCL
        from CHRT_CONTROLLIMITS
        group by CHRTID
    ) Newest on Newest.CHRTID = CHRT_CONTROLLIMITS.CHRTID
             and Newest.RecentCL = CLSTARTDATE
),
Chart_Titles_Table as (
    select CHRTID, TITLE, ENGOWNER
    from CHRT
)
select *
from Chart_Titles_Table ctt
join Control_Limit_Table clt on clt.chrtid = ctt.CHRTID;

This particular query can be improved with the use of window functions:

with Control_Limit_Table as (
    select
        clt.CHRTID,
        clt.CLSTARTDATE,
        clt.LOWERCONTROLLIMIT,
        clt.UPPERCONTROLLIMIT,
        clt.CENTRALLINE,
        MAX(clt.CLSTARTDATE) OVER (PARTITION BY clt.CHRTID, clt.CLSTARTDATE) as RecentCL
    from CHRT_CONTROLLIMITS clt
),
Chart_Titles_Table as (
    select CHRTID, TITLE, ENGOWNER
    from CHRT
)
select *
from Chart_Titles_Table ctt
join Control_Limit_Table clt on clt.chrtid = ctt.CHRTID;

Note: I strongly recommend the use of short aliases and whitespace to make queries more readable

SQL How do I name tables to refer to them later?

1 Answers1