Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

How do I JOIN in a third table?

So I have this query:

SELECT CPS.NodeID, N.Caption, N.IPAddress, CPS.Status AS SerialNumber, N.Vendor, N.Location, N.Contact, N.IOSVersion, N.MachineType, N.NodeDescription, N.LastBoot
FROM Orion.NPM.CustomPollerStatusOnNode AS CPS
JOIN Orion.Nodes N ON CPS.NodeID = N.NodeID
WHERE CPS.DisplayName LIKE '%Module Level1%'

Which, although ugly, it works.

But I also need to pull some date from the Orion.NodesCustomProperties table and having only just got my head around JOINing one table in, I have no idea how to JOIN another one in - and t'internet is just hurting my brain at present.

I can see that 'NodeID' is common across all 3 if that helps?

Suggestions please?

Find more posts tagged with

Accepted answers

stuartd

After multiple attempts I finally managed to suss this out.

So for posterity, or for the benefit of others, then here is an example that JOINs:

- Orion.AlertHistory [AH] - this is the base table queried
- Orion.Nodes [NS]- JOINed so as to get human readable names of nodes included
- Orion.ResponseTime [RT] - JOINed so as to extract the percentage down field.

SELECT TOP 1000 NS.Caption, RT.PercentDown, AH.AlertHistoryID, AH.EventType, AH.Message, AH.TimeStamp, AH.AccountID, AH.AlertActiveID, AH.AlertObjectID, AH.ActionID
FROM Orion.AlertHistory AS AH
JOIN Orion.ResponseTime RT ON RT.NodeID = AH.AlertObjectID
JOIN Orion.Nodes NS ON NS.NodeID = AH.AlertObjectID
WHERE PercentDown = '100'

I realise the example is likely neither elegant, nor efficient, but it does work and gives me a basis on which to move forward.

All comments

stuartd

After multiple attempts I finally managed to suss this out.

So for posterity, or for the benefit of others, then here is an example that JOINs:

SELECT TOP 1000 NS.Caption, RT.PercentDown, AH.AlertHistoryID, AH.EventType, AH.Message, AH.TimeStamp, AH.AccountID, AH.AlertActiveID, AH.AlertObjectID, AH.ActionID
FROM Orion.AlertHistory AS AH
JOIN Orion.ResponseTime RT ON RT.NodeID = AH.AlertObjectID
JOIN Orion.Nodes NS ON NS.NodeID = AH.AlertObjectID
WHERE PercentDown = '100'

I realise the example is likely neither elegant, nor efficient, but it does work and gives me a basis on which to move forward.

bobmarley

Excellent example! Used it for joining my World Map tables today to get Node, Custom Properties, and Latitude and Longitude all into one report.

Select NodeID, IP_Address, Caption, Latitude, Longitude, CustomProperty1, CustomProperty2, CustomProperty3, CustomProperty4, InstanceID, Status

FROM Orion.NodesCustomProperties AS cp

JOIN Orion.nodes nd on cp.NodeID = nd.NodeID

JOIN Orion.Worldmap.Point NS ON NS.InstanceID = CP.NodeID

Order by IP_Address ASC

stuartd

Thank you - the pain I went through trying to understand this little bit of SQL and now it's like - yeah, I can do that