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
Store

Implicit Joins - how please in simple terms?

I realise I've had answers that include implicit joins but no matter how many times I read them I just can't get my head around creating a query of my own from scratch that uses this functionality.

So in SWQL Studio I want to run a report showing me the licensed status of nodes and then JOIN in the caption names. I can do this with traditional JOINs but even though Orion.Nodes is in the list as a "chain" and no matter what format I use, I just can't seem to get a result that works.

So the base code is:

SELECT TOP 1000 NodeID, LicensedByNCM
FROM Cirrus.NCMNodeLicenseStatus

As I said above, Orion.Nodes is shown as a chained (or implicit - is that the right phrase?) JOIN.

So what do I type to be able to access the 'Caption' field from Orion.Nodes please?

Whilst here, as I think this is connected... how many implicit joins can I do in any one query? i.e. looking at Orion.Nodes shows over 100 'chained' links - if a query demanded it (no, I don't expect I'll ever be at that level) then is there a limit on how many of these could be 'JOINed' in using the implicit links?

p.s. apologies if not the right forum.

image_discussion93588-1.png

Find more posts tagged with

Accepted answers

tony.johnson

The way I would approach this would be to start with the Nodes entity, as you mentioned "I want to run a report showing me the licensed status of nodes"

So let's start with getting the caption and description for some nodes

SELECT Caption, Description from Orion.Nodes

The key to navigating or linking the other entities like Cirrus.NCMNodeLicenseStatus is to alias the Orion.Nodes entity, you will typically see this written as

SELECT Caption, Description from Orion.Nodes N

Notice the additional N. Orion.Nodes can be referenced simply as N. You can choose any alias and may come across ND in some examples. I like to use this alias in the fields I am selecting also as it makes it easier for me to understand

SELECT n.Caption, n.Description from Orion.Nodes N

Now that we have an alias for Orion.Nodes we can 'Navigate' any of the linked entities shown such as interfaces, Volumes, and NCMLicenseStatus by simply including the fields of interest for those entities such as n.Volumes.Caption, n.Interfaces.Caption or in this case n.NCMLicenseStatus.LicensedByNCM so the final query becomes

SELECT n.Caption, n.Description, n.NCMLicenseStatus.LicensedByNCM
from Orion.Nodes N

Multiple navigation properties can be used in this example as there are 2 fields with the same name they have been aliased also to help distinguish them

SELECT n.Caption, n.Description, n.NCMLicenseStatus.LicensedByNCM, N.Volumes.Description as [Vol Description], N.Interfaces.Description as [Interface Description] from Orion.Nodes N

All comments

jerrysInge

SELECT TOP 1000 NodeID, ncmnls.LicensedByNCM,ncmnls.OrionNode.Caption

FROM Cirrus.NCMNodeLicenseStatus As ncmnls

Basically if you want to use the inner join then append the "chain name" (in your case OrionNode) to the entity

tony.johnson

The way I would approach this would be to start with the Nodes entity, as you mentioned "I want to run a report showing me the licensed status of nodes"

So let's start with getting the caption and description for some nodes

SELECT Caption, Description from Orion.Nodes

The key to navigating or linking the other entities like Cirrus.NCMNodeLicenseStatus is to alias the Orion.Nodes entity, you will typically see this written as

SELECT Caption, Description from Orion.Nodes N

SELECT n.Caption, n.Description from Orion.Nodes N

SELECT n.Caption, n.Description, n.NCMLicenseStatus.LicensedByNCM
from Orion.Nodes N

Multiple navigation properties can be used in this example as there are 2 fields with the same name they have been aliased also to help distinguish them

SELECT n.Caption, n.Description, n.NCMLicenseStatus.LicensedByNCM, N.Volumes.Description as [Vol Description], N.Interfaces.Description as [Interface Description] from Orion.Nodes N

m_roberts

I just wanted to add to this, as this is a great write up on navigating through the entity structure, in that you can continue down the path chain.

SELECT n.Caption, n.Description, n.NCMLicenseStatus.LicensedByNCM, N.Volumes.Description as [Vol Description], N.Volumes.SRMTopologies.StorageArray.Caption from Orion.Nodes N

In the above example, you can see I have navigated to the SRM modules data to identify which storage Array the Volume is served from. This is a fabulous way of building out queries, with the question of where to start sometimes being the most difficult to get all the data objects populated without dealing with implicit JOINS.

stuartd

Thank you @tony.johnson for a most comprehensive write up, and thank you also to @jerrysInge and @m_roberts for the extra info.

After working through it at my end I think I am finally starting to see the light.

The important (I think) part is the aliasing - which I do use when doing JOINs, but the main problem I was having with your explanation is that for the life of me I couldn't see 'NCMLicenseStatus' in the linked properties under Orion.Nodes - until I did a search for the word license I just wasn't seeing this:

Which is why I was starting at the other end of equation. But just to prove I understood what you are doing, I crafted my query thus:

Which worked just fine.

So THANK you again, all of you.