SQL Join two query

Question

enter image description here

I need some help combining these two queries so I can get this in one single view.

Query 1

select t.*, n.caption, n.description
from (
    select NodeID, 
              count(distinct cpuindex) as number_of_cpu, 
                case 
                           When count(distinct cpuindex) < 8 THEN 1
                           Else count(distinct cpuindex)/8 
                           End AS number_of_cores
    from CPUMultiLoad_Detail (nolock) where nodeid in (select nodeid from nodesdata)
       group by NodeID
   ) as t
inner join NodesData as n (nolock) on n.nodeid = t.nodeid
where n.description NOT Like '%Windows%'
order by n.description

Query 2

SELECT D.Environment, B.Name, C.Caption, A.ComponentStatisticData, A.ErrorMessage
FROM [APM_CurrentStatistics] A, APM_Application B, NodesData C
join NodesCustomProperties D on D.NodeID= C.NodeID
WHERE 
A.ApplicationID=B.ID AND
A.NodeID=C.NodeID AND 
B.Name IN ('Oracle Database Licensing')

I want to join first query and second query so I have CPU Information and Licensing Information in same table. How do I join both query? We can use common key Nodes.NodeID to join and not sure how. Any help will be greatly appreciated.

Don't mix explicit and implicit joins, it's a mess. Switch to modern, explicit `JOIN` syntax everywhere! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. — jarlh, Jun 01 '20 at 19:42
What do you mean when you say "How do I join both query?" - please show some sample data and expected results because its unclear as asked. — Dale K, Jun 01 '20 at 20:53
Hi Dale, I have added screenshots.The caption is the server name on each row which is identified by the node id. Each row has one node id. — SQL Novice, Jun 01 '20 at 21:08
@SQLNovice you still haven't showed the most important part - what you want to see once they are combined. — Dale K, Jun 01 '20 at 21:13
Learn to use proper `JOIN` would be a good start. It's been around for over 25 years!!! — Eric, Jun 01 '20 at 21:48

score 2 · Accepted Answer · answered Jun 01 '20 at 21:05

Consider joining the inner aggregate subquery of first query which holds distinct NodeID to the second query using a CTE. Additionally, use explicit JOIN(current standard in SQL) and heed bad habits to kick : using table aliases like (a, b, c) and use more informative table aliases.

WITH agg AS 
   (
    select NodeID, 
           count(distinct cpuindex) as number_of_cpu, 
           case 
               when count(distinct cpuindex) < 8 THEN 1
               else count(distinct cpuindex) / 8 
           end AS number_of_cores
    from CPUMultiLoad_Detail 
    where nodeid in (select nodeid from nodesdata)
    group by NodeID
   )

SELECT cp.Environment, app.Name, n.Caption,
       cs.ComponentStatisticData, cs.ErrorMessage,
       agg.NodeID, agg.number_of_cpu, agg.number_of_cores, n.description
FROM APM_CurrentStatistics cs 
INNER JOIN APM_Application app 
  ON cs.ApplicationID = app.ID
  AND app.Name IN ('Oracle Database Licensing') 
INNER JOIN NodesData n
  ON cs.NodeID = n.NodeID
  AND n.description NOT LIKE '%Windows%'
INNER JOIN NodesCustomProperties cp 
  ON cp.NodeID = n.NodeID
INNER JOIN agg
  ON cs.NodeID = agg.NodeID
ORDER BY n.description

Thank you so much Parfait, that worked great and what I was looking for. I just need one last change. I need the component Name values "Oracle Version", "Oracle Partitioning", "Real Application Clusters (RAC)" which are currently displayed in rows and repeated for each device show up as a column instead. Is there a way it can be done? — SQL Novice, Jun 02 '20 at 14:13
Forgive me, isn't that asking a new question? Your original post does not mention such a specification. Look into pivoting data using this query as the base source. — Parfait, Jun 02 '20 at 14:41
Thank you Parfait, I will give that a try. I am new to SQL scripting and still learning. — SQL Novice, Jun 02 '20 at 14:45

SQL Join two query

1 Answers1