How to create join in Database Manager?

Question

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]

RichardLetts · Accepted Answer

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

How to create join in Database Manager?

Top Replies