Performing complex joins

The previous information covered simple join list entries expressed in terms of a Navigation. However, it is possible to write more complex joins where there is not a pre-defined Navigation. This section details those concepts and methods.

Database joins specify how to link an entry in one table with another. Assume there are two table aliases: t1, and t2. T1 has columns c1a…c1m and T2 has columns c2a…c2n. Then one typical way of linking two tables together is specifying that some column from t1, say c1c, must equal some column in t2, say c2d. If the aliases for t1 and t2 were a1 and a2 respectfully, then this might be expressed in a SQL expression as:

a1.c1c=a2.c2d

As a concrete example, consider Device as t1, which has a unique key DeviceId (this is c1d), and AccessList as t2, which has foreign key reference to the Device also called DeviceId. Then you could write a SQL expression like: device.DeviceId=acl.DeviceId where device is the alias for Device and acl is the alias for AccessList.

The result of executing this join is that it will make a composite Result Set consisting of columns from Device, joined with columns from AccessList, where the access lists are contained in the device.

There is a pre-build contains navigation written for the Device object to the AccessList object, which results in generation of the above SQL. Navigations are specified by their navigation name, which in most cases is just the Object name where we want the navigation to join to.

However, there are times when you may want to join tables for which there is no pre-build navigation. This often occurs if you are looking for relationships between different Devices. Consider the Query “Arp Ip Connectivity” as a join list.


	Alias	Expression	Navigation
ArpEntry	arp
Interface	interface	arp.ArpIpAddress=interface.IpAddress
Device	d2	interface	Device

The 2nd join entry has a user-specified complex join. Here it is required that the ArpEntry’s ArpIpAddress field exactly matches the IpAddress field in an Interface. There is nothing in the Query that requires the arp entry to be in the same Device as the interface.

So, the effect of this query is that all the Device’s arp entries are searched, and for each entry found, find an Interface that has that same IpAddress assigned to the Interface. What does this mean?

It means that the Device containing the arp entry, call it d1, has an entry for an interface potentially in another Device, call it d2. At one time d1 had physical connectivity to d2, since it has an arp entry for it.

There is a problem with this Query however; an Interface can potentially have more than one IpAddress assigned to it. The IpAddress field on the Interface is just the primary IP address. To check all the IpAddress fields, use the InterfaceIpAddress table as shown in InterfaceIpAddress table.

Table 1. InterfaceIpAddress table
	Alias	Expression	Navigation
Device	d1
ArpEntry	arp	d1	ArpEntry
InterfaceIpAddress	ipaddr	arp.ArpIpAddress=ipaddr.IpAddress
Interface	interface	ipaddr	Interface
Device	d2	interface	Device

This illustrates the point that you must become familiar with the model to write some of the Queries.

Although most explicitly specified joins use the equal operator, there is at least one case where joins with a different operator are useful, and that is where you want to test subnet inclusion using the “<<=”,or “>>=” operators introduced in the Selection tab section.

The list of possible join operators is:

Table 2. Join operators
Operator	Applicable types	Description
=	String, Integer, Long, INET, CIDR, other	Joins records where column in first table exactly same as column in second.
>	String, Integer, Long	First table column value greater than second table column value.
>=	String, Integer, Long	First table column value greater than or equal to second column value.
<	String, Integer, Long	First table column less than second table column.
<=	String, Integer, Long	First table column less than or equal second table column.
!=	String, Integer, Long	Not equals. Not sure using this makes much sense.
<<=	INET Note: You cannot join a string with an INET or CIDR value.	Subnet in left CIDR value a subset of or equal to subnet in right CIDR value.
>>=	INET Note: You cannot join a string with an INET or CIDR value.	Subnet in left CIDR contains or is exactly equal to subnet in right table CIDR.

Note:

For the above table, left and right refers to which table is on the left or right side of the operator. For example, t2.c2d >>= t1.c1c says that the subnet in t2.c2d contains or is exactly equal to, the subnet in t1.c1c.

The INET column (display) names, by convention, end with INET or CIDR, e.g. IpAddressCIDR. You may not join IpAddress (which is a string) with an IpAddressCIDR field using the INET or CIDR specific operators.

As a final example of a complex Query, look at the Join list for the following Query (called “Ospf Area to Area”.

Example of a complex join shows an example of a complex join from the OspfAreaNetwork network to the InterfaceIpAddress ipaddress, where the InterfaceIpAddress subnet is a subset of, or equal to, the subnet of the OspfAreaNetwork.

The intent of this query is to look at the OspfAreaNetwork entries on the first Device device, and find Interfaces that contain IpAddress settings that are within the OspfAreaNetwork’s subnet.