CODEWITHSUNDEEP
sqldatabasesql-server-2008
Satish
Satish

Reputation: 17467

SQL Join two query results

First SQL Query (Total Memroy)

SELECT 
Nodes.NodeID AS NodeID,  Nodes.TotalMemory AS TotalMemory, Nodes.Caption AS NodeName
 FROM 
(Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID))

Second SQL Query (Number of CPU on nodes) 

SELECT N.Caption, COUNT(DISTINCT CPUIndex)

FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)

JOIN Nodes N ON CPU.NodeID = N.NodeID

GROUP BY
N.Caption
Order By
N.Caption

I want to join first query and second query so i have both Total memory and Number of CPU on node in same table. How do i join both query? we can use common key Nodes.NodeID to join but how?

Upvotes: 1

Views: 9464

Answers (4)

shinek
shinek

Reputation: 251

SELECT t1.col2,t2.col3 FROM (SELECT NodeID col1,TotalMemory col2,Caption col3 FROM Nodes)t1 join
(SELECT NodeID col1,Caption col2,COUNT(DISTINCT CPUIndex) col3 FROM CPUMultiLoad_Detail CPU WITH(NOLOCK) GROUP BY Caption Order By Caption)t2 ON t1.col1=t2.col1

Upvotes: 0

Hogan
Hogan

Reputation: 70538

In the general case just use a join and a CTE

WITH PartOne AS
(
  SELECT N.NodeID, COUNT(DISTINCT CPU.Index) as [Count]
  FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)
  JOIN Nodes N ON CPU.NodeID = N.NodeID
  GROUP BY N.Caption
)
SELECT Nodes.NodeID 
       Nodes.TotalMemory , 
       Nodes.Caption AS NodeName,
       PartOne.[Count]
FROM Nodes 
INNER JOIN Volumes ON Nodes.NodeID = Volumes.NodeID
JOIN PartOne ON PartOne.NodeID = Nodes.NodeID

This particular case can also be done in one query like this:

SELECT Nodes.NodeID 
       Nodes.TotalMemory , 
       Nodes.Caption AS NodeName,
       COUNT(DISTINCT CPU.Index) OVER (PARTITION BY Nodes.Caption ORDER BY Nodes.Caption) AS [Count]
FROM Nodes 
JOIN Volumes ON Nodes.NodeID = Volumes.NodeID
JOIN CPUMultiLoad_Detail CPU ON  CPU.NodeID = Nodes.NodeID

Upvotes: 3

Stefano Bafaro
Stefano Bafaro

Reputation: 913

For the same situation i used the Temp Table

SELECT ..... FROM.... INTO #Temp1

GO

SELECT ..... FROM.... INTO #Temp2

GO

SELECT ..... FROM #Temp1, #Temp2 WHERE #Temp1.NodeID = #Temp2.NodeID

Remember to Drop the Temp Tables like This:

DROP Table #Temp1 DROP Table #Temp2

Upvotes: 1

LittleSweetSeas
LittleSweetSeas

Reputation: 7084

"With" comes in help to have a clean query:

    WITH CPUOnNodes AS (    
       SELECT N.NodeID, N.Caption, COUNT(DISTINCT CPUIndex)
       FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)
       JOIN Nodes N ON CPU.NodeID = N.NodeID
       GROUP BY N.NodeID, N.Caption
       Order By N.Caption
    )
    SELECT N.NodeID, N.TotalMemory, N.Caption NodeName
    FROM Nodes N 
    INNER JOIN Volumes V ON N.NodeID = V.NodeID
    INNER JOIN CPUOnNodes C ON N.NodeID = C.NodeID

Upvotes: 0

Related Questions