How to create join in Database Manager?

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


select Caption from NodesData

where the NodeID.NodeIPAddresses =  NodeID.NodesData

So, I can get a query that looks like:

Interface          IPAddress          Caption

Gi3/6                      MyRouterA

Po1                        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]

  • go out and buy SQL for Dummies.

    Join Fundamentals


    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]


    I.IfName as Interface

    ,IP.IPAddress as 'IP Address'


    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

  • ‌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

  • 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:

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

    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.

  • 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

    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.



  • 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.

  • emoticons_happy.png 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 ...  emoticons_wink.png

    Best Luck,


  • 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


    nip.NodeID = nd.NodeID


    nd.NodeID = ncp.NodeID



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