Search code examples
sql-serversubqueryuniontemp-tablessql-server-2014-express

SQL server Temp table with joins inside other select


I have the following structure:

Create @temp
Select ...inser...into @temp where ...

(select ... from @temp
Join tblA where ... )
UNION
(Select ... from @temp
join tblB where ... )

After build above table I need to be able to perform WHERE, JOINS, ...

Something like:

Select ... from (above statement)
join ....
where....

I don't know how of if a @temp,joins, union... can be inside other select.

OR only thing I can do is create a @Temp2 inserting with first statement result and then work with other join,where... ?

UPDATE 1:

I also trying:

With cte (query returned columns)
as
(same query I was using to build my @temp as before)

(select ... from cte
join tblA
where...)
UNION
(select ... from cte
join tblB
where...)

But Im stuck at same point in how to perform other joins, where... with above total result


Solution

  • Actually you can do it without temp-table:

    WITH myCTE [ ( column_name [,...n] ) ]
    AS
    ( here you define your query )
    

    and after that you just do your Select but use CTE

    Select ... from myCTE
    join ....
    where....
    

    about CTE you can read Here After Update

    Select fields from myCTE join table1
    Union
    Select fields from myCTE join table2
    

    Without brackets in your query