In temporary tables/variables: join multiple rows to table with only 1 row

Hi, I have a small problem of joining a temporary variable with a temporary table. Any input would be much appreciated. I present the problem in the order in which I have tried to solve it.

First of, I have a temporary variable which is created from a select statement. The variable @enhet has 2 rows/observations (in my test file, later it will be many more):

declare @enhet varchar(50)

SELECT @enhet = 
     A.[EnhetsId]
FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
inner join (
            select [EnhetsId], max(SenastUppdaterad) as SenastDatum
            from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
            group by [EnhetsId]
            ) B
on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
WHERE [NyStatus] = 4

Secondly, I want to create a temporary table that combine these two observations (1 variable) with extra variables that are the same for both unique observations of variable @enhet. The aim is to have a table with unique values only for the variable @enhet but the rest is the same.

declare @temp2 table (
    EnhetsId varchar(50), 
    TjanstId Int, 
    Tabell varchar(50),
    Kommentar ntext,
    Uppdaterad datetime
)
insert into @temp2 (
    EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
    values (
        @enhet, 1, 'GR_PS09_1', 'KLAR', getdate())

select * from @temp2

The problem is that when I run the script the output is only 1 row with just the last observation from the @enhet-variable.

Is there anyone who knows what to do? I have tried many things but since I am new to SQL I have failed to write the correct scripts. Can anyone point me in the right direction?

Thanks in advance and best regards! :)

Answers


Use a common table expression to hold the results of your first joined query:

declare @temp2 table (
   EnhetsId varchar(50), 
   TjanstId Int, 
   Tabell varchar(50),
   Kommentar ntext,
   Uppdaterad datetime
);

WITH ENHET_CTE AS 
(
    SELECT A.[EnhetsId]
    FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
    inner join (
             select [EnhetsId], max(SenastUppdaterad) as SenastDatum
             from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
             group by [EnhetsId]
             ) B
    on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
    WHERE [NyStatus] = 4
)

insert into @temp2 
    (EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
SELECT 
    EnhetsId, 1, 'GR_PS09_1', 'KLAR', getdate() 
from ENHET_CTE;

select * from @temp2;

Need Your Help

Can PNG image transparency be preserved when using PHP's GDlib imagecopyresampled?

php png transparency gd alpha

The following PHP code snippet uses GD to resize a browser-uploaded PNG to 128x128. It works great, except that the transparent areas in the original image are being replaced with a solid color- bl...

How to do natural join in XQuery

xml join xquery natural-join

My goal with this clause is to list the employees that live in San Fran but work in Silicon Valley. Right now my clause is working to find out which employees live in San Fran, but I am not sure ho...