Need to join two tables

Both queries give me the results I need in separate tables, now I just need help with the formatting and syntanx for it to work properly in ONE table. Can anyone help me out with this join?

FROM Orion.APM.SqlServerApplication n

SELECT n.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn,n.CustomProperties.City AS CustomProperties_City,n.CustomProperties.State AS CustomProperties_State

FROM Orion.Nodes n

Find more posts tagged with

swql query

inner_join

help

Join

SWQL

Modern Dashboards

modern widgets

Accepted answers

All comments

lynchnigel

Something like this ?

https://thwack.solarwinds.com/product-forums/network-performance-monitor-npm/f/forum/90438/swql---combing-two-left-join-tables-into-one

stuartd

I don't have APM so I can't provide a 100% answer but you need to find a commonality between the two tables. Typically that would be NodeID or similar.

On the presumption that NodeID is a commonality between the two tables then try this:

Change your FROM line to read:

FROM Orion.APM.SqlServerApplication AS n

Then follow that line with:

JOIN Orion.NodesCustomProperties AS ncp ON n.NodeID = ncp.NodeID

Finally, in your select statement put in any records you want but preface them with ncp.

Note: I've joined you to NodesCustomProperties as it appears all the records you are pulling are form that table .

xaturbatu

You can directly call the data without any join, SWQL can do that.

SELECT n.FullyQualifiedName,n.ApplicationAlert.ApplicationAvailability AS ApplicationAlert_ApplicationAvailability,n.ApplicationAlert.ComponentsWithProblems AS ApplicationAlert_ComponentsWithProblems,n.DetailsUrl, n.node.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn,n.node.CustomProperties.City AS CustomProperties_City, n.node.CustomProperties.State AS CustomProperties_State

FROM Orion.APM.SqlServerApplication n

Martin_Crothers

Hi @mrahmed6979,

I'm assuming that you need help primarily with how to do SQL queries, so apologies if that is not true and this seems a bit pedantic.

Below I'll:

Show you how to structure a join statement and work with multiple tables
Show you how to avoid the joins in SWQL where navigational properties are available

1: How to do a join

When joining tables you will need to know what the Primary Keys and Foreign Keys of the tables you're working with are. The primary key is the unique identifier of a Record (aka row) in a relational database. When that record is referenced in a different table, that primary key is the data point used to point at it, making it a foreign key.

To create a JOIN statement, you must also include an ON statement that instructs the join what those primary to foreign key relationships are. That would look like this:

Let's assume that you have these two tables:

TABLE1:

NodeID	Caption	IP_Address
100	server_A	192.168.1.100
101	switch_1	192.168.1.101

TABLE2:

AppID	Name	NodeID
1	SQL	100
2	Website	100
3	SQL	102

The commonality between them is the NodeID column. In Table1 it is the Primary Key, and in Table2 it is the Foreign Key. This is the column to join on. So, if we want to get all the application names that are related to server_A, we would write a statement like this:

SELECT
    TBL1_ALIAS.Caption
,   TBL2_ALIAS.Name
FROM TABLE1 AS TBL1_ALIAS

INNER JOIN TABLE2 AS TBL2_ALIAS
ON TBL1_ALIAS.NodeID = TBL2_ALIAS.NodeID

WHERE TBL1_ALIAS.Caption = 'server_A'

The result of this query would be:

Caption	Name
server_A	SQL
server_A	Website

Now, when assigning an alias for the table the AS statement is not required. In SWQL studio I've found that if I don't use it then the intellisense works better.

So, let's look at your two queries and see how we can get them to join. (I've edited their alignment to make it easier to read)

Query1:

SELECT
	n.FullyQualifiedName
,	n.ApplicationAlert.ApplicationAvailability AS ApplicationAlert_ApplicationAvailability
,	n.ApplicationAlert.ComponentsWithProblems AS ApplicationAlert_ComponentsWithProblems
,	n.DetailsUrl
FROM Orion.APM.SqlServerApplication n

Query2:

SELECT
	n.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn
,	n.CustomProperties.City AS CustomProperties_City
,	n.CustomProperties.State AS CustomProperties_State
FROM Orion.Nodes n

Let's try to find the primary key/foreign key between them. The easiest way to do so will be via SWQL Studio, and expanding the Entity to show the Properties:

The icon that looks like a key is the primary key of this entity. So the primary key of entity Orion.APM.SqlServerApplication is ApplicationID. If you look at the Orion.Nodes entity you'll see that NodeID is the primary key there. And lower down in the list of properties of the Orion.APM.SqlServerApplication is the column named NodeID, that's the foreign key.

So, to rewrite your two queries joined, we would join on the NodeID. To do this I will change the alias on Orion.APM.SqlServerApplication from n to SSA. (I'm using capitals as that is SQL tradition, it's a shouty language).

SELECT
	SSA.FullyQualifiedName
,	SSA.ApplicationAlert.ApplicationAvailability AS ApplicationAlert_ApplicationAvailability
,	SSA.ApplicationAlert.ComponentsWithProblems AS ApplicationAlert_ComponentsWithProblems
,	SSA.DetailsUrl
,	n.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn
,	n.CustomProperties.City AS CustomProperties_City
,	n.CustomProperties.State AS CustomProperties_State
FROM Orion.APM.SqlServerApplication SSA
INNER JOIN Orion.Nodes n
ON SSA.NodeID = n.NodeID

I don't have any of these in my environment, so I can't validate that the join is working correctly, but it's correct in concept.

2. Using navigational properties:

If you look further down in the list of properties for the entity Orion.APM.SqlServerApplication you will see this:

That chain link icon is for navigational properties. Instead of using join, you could do this instead:

SELECT
	SSA.FullyQualifiedName
,	SSA.ApplicationAlert.ApplicationAvailability AS ApplicationAlert_ApplicationAvailability
,	SSA.ApplicationAlert.ComponentsWithProblems AS ApplicationAlert_ComponentsWithProblems
,	SSA.DetailsUrl
,	SSA.Nodes.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn
,	SSA.Nodes.CustomProperties.City AS CustomProperties_City
,	SSA.Nodes.CustomProperties.State AS CustomProperties_State
FROM Orion.APM.SqlServerApplication SSA

There it is just chaining together the Orion.APM.SqlServerApplication > Orion.Nodes > Orion.NodesCustomProperties relationships.

Hope this is helps!

mrahmed6979

Thank you so much for this explanation im getting the jist of it.

So I tried running the query so basically when i got to this part it doesnt allow me to execute.

SELECT

SSA.FullyQualifiedName

, SSA.ApplicationAlert.ApplicationAvailability AS ApplicationAlert_ApplicationAvailability

, SSA.ApplicationAlert.ComponentsWithProblems AS ApplicationAlert_ComponentsWithProblems

, SSA.DetailsUrl

, n.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn

, n.CustomProperties.City AS CustomProperties_City

, n.CustomProperties.State AS CustomProperties_State

FROM Orion.APM.SqlServerApplication SSA

INNER JOIN Orion.Nodes n

ON SSA.NodeID = n.NodeID

I ran it without the highlighted portion and the query works but then i dont get those important fields im looking for

Martin_Crothers

hmmm. Does your original query for just those work?

SELECT
	n.CustomProperties.Stedsnavn AS CustomProperties_Stedsnavn
,	n.CustomProperties.City AS CustomProperties_City
,	n.CustomProperties.State AS CustomProperties_State
FROM Orion.Nodes n

mrahmed6979

This query alone does work yes.