CODEWITHSUNDEEP
sqlselectjoinunionintersect
Alex Martin
Alex Martin

Reputation: 196

SQL union / join / intersect multiple select statements

I have two select statements. One gets a list (if any) of logged voltage data in the past 60 seconds and related chamber names, and one gets a list (if any) of logged arc event data in the past 5 minutes. I am trying to append the arc count data as new columns to the voltage data table. I cannot figure out how to do this.

Note that, there may or may not be arc count rows, for a given chamber name that is in the voltage data table. If there are no rows, I want to set the arc count column value to zero.

Any ideas on how to accomplish this?

Voltage Data:

SELECT DISTINCT dbo.CoatingChambers.Name, 
AVG(dbo.CoatingGridVoltage_Data.ChanA_DCVolts) AS ChanADC,             
AVG(dbo.CoatingGridVoltage_Data.ChanB_DCVolts) AS ChanBDC, 
AVG(dbo.CoatingGridVoltage_Data.ChanA_RFVolts) AS ChanARF, 
AVG(dbo.CoatingGridVoltage_Data.ChanB_RFVolts) AS ChanBRF FROM 
dbo.CoatingGridVoltage_Data LEFT OUTER JOIN dbo.CoatingChambers ON 
dbo.CoatingGridVoltage_Data.CoatingChambersID = 
dbo.CoatingChambers.CoatingChambersID WHERE 
(dbo.CoatingGridVoltage_Data.DT > DATEADD(second, - 60, 
SYSUTCDATETIME())) GROUP BY dbo.CoatingChambers.Name

Returns

Name |      ChanADC      |       ChanBDC      |       ChanARF       |     ChanBRF
-----+-------------------+--------------------+---------------------+------------------
OX2  | 2.9099999666214   | -0.485000004371007 | 0.344801843166351   | 0.49748428662618
S2   | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112
S3   | 4.25666658083598  |  0.5               | 0.96554297208786    | 0.134956782062848

Arc count table:

SELECT CoatingChambers.Name,
SUM(ArcCount) as ArcCount
FROM CoatingChambers
LEFT JOIN CoatingArc_Data
ON dbo.[CoatingArc_Data].CoatingChambersID = dbo.CoatingChambers.CoatingChambersID 
where EventDT > DATEADD(mi,-5, GETDATE())
Group by Name

Returns

Name | ArcCount
-----+---------
L1   |   283
L4   |     0
L6   |     1
S2   |    55

To be clear, I want this table (with added arc count column), given the two tables above:

Name |      ChanADC      |       ChanBDC      |       ChanARF       | ChanBRF           | ArcCount
-----+-------------------+--------------------+---------------------+-------------------+---------
OX2  | 2.9099999666214   | -0.485000004371007 | 0.344801843166351   | 0.49748428662618  |  0
S2   | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112 | 55
S3   | 4.25666658083598  |  0.5               | 0.96554297208786    | 0.134956782062848 |  0

Upvotes: 0

Views: 696

Answers (1)

Clay
Clay

Reputation: 5084

You can treat the select statements as virtual tables and just join them together:

select 
  x.Name,
  x.ChanADC, 
  x.ChanBDC,  
  x.ChanARF,
  x.ChanBRF,
  isnull( y.ArcCount, 0 ) ArcCount
from
(
  select distinct 
    cc.Name, 
    AVG(cgv.ChanA_DCVolts) AS ChanADC,     
    AVG(cgv.ChanB_DCVolts) AS ChanBDC,
    AVG(cgv.ChanA_RFVolts) AS ChanARF, 
    AVG(cgv.ChanB_RFVolts) AS ChanBRF 
  from
    dbo.CoatingGridVoltage_Data cgv 
      left outer join   
    dbo.CoatingChambers cc 
      on 
        cgv.CoatingChambersID = cc.CoatingChambersID 
  where 
    cgv.DT > dateadd(second, - 60, sysutcdatetime())
  group by 
    cc.Name
) as x

  left outer join

(
  select 
    cc.Name,
    sum(ac.ArcCount) as ArcCount
  from 
    dbo.CoatingChambers cc 
      left outer join 
    dbo.CoatingArc_Data ac 
      on 
        ac.CoatingChambersID = cc.CoatingChambersID 
  where 
    EventDT > dateadd(mi,-5, getdate())
  group by 
    Name    
) as y

  on 
    x.Name = y.Name

Also, it's worthwhile to simplify your names with aliases and format the queries for readability...which I shamelessly took a stab at.

Upvotes: 1

Related Questions