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

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]

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

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

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


Reply Children
No Data