cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

How to create join in Database Manager?

Jump to solution

Hi All,

I'm trying to become a "power" user, and do a fancy SQL query, but it's not working for me.  I can't find a discussion about doing a basic join.

In English pseudocode, I'm trying to:

select IfName as Interface from Interfaces

select IPAddress as 'IP Address' from NodeIPAddresses

where the InterfaceIndex.Interfaces = InterfaceIndex.NodeIPAddresses

and

select Caption from NodesData

where the NodeID.NodeIPAddresses =  NodeID.NodesData

So, I can get a query that looks like:

Interface          IPAddress          Caption

Gi3/6               1.2.3.4                 MyRouterA

Po1                  5.6.7.8                MySwitchB

So, I'm trying to write a query which will selct the interface name from Interfaces, the IP Address from Node IP Addresses, and the Caption from NodesData.

I can manage A select statement, but don't know how to do the joins.

SELECT IfName as Name, InterfaceAlias as Description, NodeID, InterfaceID, InterfaceIndex FROM [dbo].[Interfaces]

Labels (1)
0 Kudos
1 Solution
Level 16

go out and buy SQL for Dummies.

Join Fundamentals

basically:

say what you want to get out of the tables

Say which tables you want the information from

Say how the tables relate to each other [On clause]

Say how you want to restrict the result set Further [Where]

select

I.IfName as Interface

,IP.IPAddress as 'IP Address'

,N.Caption

from NodesData N

INNER JOIN Interfaces I

on N.Nodeid=I.Nodeid

INNER JOIN NodeIPAddresses IP

on N.NodeID=IP.NodeiD

and I.Interfaceindex=IP.InterfaceIndex

View solution in original post

11 Replies
Level 12

So, here's my SQL query to pull from 4 tables and build a list of interfaces, with names, IP's, subnets.  Next up on my to do, is to put it together (in SQL) to assemble a reverse DNS name.  ( I already exported it to CSV and did that in an Excel table).  Thanks for everyone's help.

SELECT i.NodeID as "NodeID", i.InterfaceName as "Int Name", i.Caption, i.InterfaceAlias as Description, i.IfName,
nip.IPAddress as "IP Address", nip.SubnetMask as "SubnetMask",
nd.Caption as "Node Name", 
ncp.Comments as "Node Comments"

From [Interfaces] as i, [NodeIPAddresses] as nip, [NodesData] as nd, [NodesCustomProperties] as ncp

where i.NodeID = nip.NodeID

and

nip.NodeID = nd.NodeID

and

nd.NodeID = ncp.NodeID

and

i.InterfaceIndex=nip.InterfaceIndex

0 Kudos

Ps.  I'm working on writing a blog about this, but Thwack is giving me an error about posting the blog article.

0 Kudos

Nice replies here - thank you guys.

Here is my approach: JFDI

... works exceptionally great.

I hate reading manuals and I find it very boring to study admins guide and other stuff which I might never need in practical world. Waste of time - you only have 80 odd years to live, ~100 if you eat well! When you buy a new gadget (phone, hoover, USB fan) - how often you open technical manual? The best way to learn is to Just "Freaking" Do It.

1. You have task above - you post it here - you Google it - you scratch your head - you *maybe* read technical guide at this point on this particular topic - then you get your answer.

2. Then you go and implement this for your client/employer.

3. Next - is very very important - you celebrate! I, for example, say "Yes!" out-loud - all the time. I have JOINed two tables! Yes! Yes! Yes!

4. Last - you never stop improving - you come up with new idea - then you go and take same approach.

... then you get momentum ... then you are unstoppable ... then you suddenly become this "power" user doing fancy SQL queries ... 

Best Luck,

Alex

0 Kudos
Product Manager
Product Manager

Hello psuedocyber,

If you are looking to be more proficient in SQL I would suggest you download MS SQL Management Studio which can be found here http://www.microsoft.com/en-ie/download/details.aspx?id=42299

Database manager, while useful, is not designed to handle advanced SQL queries. Its primary purpose is to provide table editing without the requirement to code and update statements.

Thanks,

Tony

0 Kudos

AGREED! APPROVED! BLESSED!

0 Kudos
Level 12

Interface_IP_Query.JPG

0 Kudos
Level 17

‌is your goal to get familiar with SQL or build the report?

if former,

you can use the 'legacy' report writer utility on the solarwinds server to build these kind of reports along with your filters. The edit, view SQL menu option will reveal the SQL for you.

if latter,

both the legacy and the new report writers have the ability to create reports like these without any need to learn about SQL

Thanks HolyGuacamole.  My goal is to extract useful info from Orion.

I'd like to understand how to do the SQL, as when I watch videos or attend SW class, it seems that's where the real experts use to get info out.  And I don't have much success at the report writing - not understanding where the data is.  At least with the db Manager, I can click on a table and see what's in it.

0 Kudos
Level 16

go out and buy SQL for Dummies.

Join Fundamentals

basically:

say what you want to get out of the tables

Say which tables you want the information from

Say how the tables relate to each other [On clause]

Say how you want to restrict the result set Further [Where]

select

I.IfName as Interface

,IP.IPAddress as 'IP Address'

,N.Caption

from NodesData N

INNER JOIN Interfaces I

on N.Nodeid=I.Nodeid

INNER JOIN NodeIPAddresses IP

on N.NodeID=IP.NodeiD

and I.Interfaceindex=IP.InterfaceIndex

View solution in original post

Richard, thanks!   This works great!

So, the letters are table aliases?  Like I.IfName?

From Join Fundamentals,

When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. This is shown in the previous example. Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. The readability of the query is improved if all columns are qualified with their table names. The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:

0 Kudos

yes...

I like to use table aliases because it makes referring to specific values shorter and I am a poor typist

typically I use the initials of the table name, e.g. CPA, CPS, CP instead of CustomPollerAssignment, CustomPollerStatus, and CustomPoller.


0 Kudos