JOIN

A table join is an operation that combines rows from two or more tables. You can join as many tables as you want within one JOIN statement. When you use a table JOIN in a SELECT statement, you must specify the column name and table when selecting the columns that you want to compare. The syntax for table joins follows standard SQL language format. The Historian OLE DB Provider supports the following table joining operations in SELECT statements.

Table 1. Supported Join Operations
Supported Join Feature	Description
Inner Join	Combines records from two tables whenever there are matching values.
Left Join or Left Outer Join	Returns all of the rows from the left (first) of two tables, even if there are no matching values for records in the right (second) table.
Right Join or Right Outer Join	Returns all of the rows from the right (second) of two tables, even if there are no matching values for records in the left (first) table.
Full Join or Outer Join	Returns all rows in both the left and right tables. Any time a row has no match in the other table, `SELECT` list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
Cross Join	Returns all rows from the left table. Each row from the left table is combined with all rows from the right table.
Old Join syntax	Simply selects columns from multiple tables using the `WHERE` clause without using the `JOIN` keyword.

Table joins are a powerful tool when organizing and analyzing data. A few examples are included in this section. However, refer to the documentation for your third-party reporting software for more complete information on building more complex queries.

JOIN Operations Rules

The following rules apply when working with JOIN operations for the Historian OLE DB Provider:

You cannot join a table with itself.
You cannot join any table with the ihTrend or ihQuerySettings tables.

The following examples display different types of joins with the ihComments table. Comments themselves are not usually that useful unless they are combined with data, as you do with the JOIN statements in the following examples.

Example 1: Perform an Inner Join to Retrieve Only Data With Associated Comments

SELECT d.timestamp, d.tagname, d.value, c.username, c.comment
FROM ihrawdata d INNER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*'
ORDER BY d.timestamp, d.tagname, c.username, c.comment

Example 2: Perform a Left Outer Join to Retrieve All Data With and Without Comments

SELECT d.timestamp, d.tagname, d.value, c.comment FROM ihrawdata d
LEFT OUTER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*' ORDER BY d.timestamp, d.tagname

Example 3: Perform a Right Outer Join to Retrieve All Comments and Their Accompanying Data

SELECT d.tagname, d.timestamp, d.value, c.comment FROM ihrawdata d
RIGHT OUTER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*' ORDER BY d.tagname, d.timestamp

Example 4: Perform a Cross Join

SELECT * FROM ihCollectors CROSS JOIN ihArchives

Example 5: Perform a Cross Join (Older Syntax)

SELECT ihTags.Tagname, iharchives.Filename FROM ihTags, ihArchives

Example 6: Join the ihMessages and ihArchives Tables

This example uses SET StartTime before the SELECT statement. The SET statement is necessary because the timestamp criteria in SELECT do not narrow down the time range for the ihMessages table until after the results have been collected and the join takes place.

SET starttime='1-jan-2000'
SELECT a.starttime, a.endtime, m.*
FROM ihmessages m JOIN iharchives a
ON m.timestamp>=a.starttime
AND m.timestamp<=a.endtime WHERE a.iscurrent=true

Example 7: Interleave Data and Messages by Timestamp

SELECT d.timestamp, m.timestamp, d.tagname, m.messagestring, d.value FROM ihRawData d FULL OUTER JOIN ihMessages m
ON d.timestamp=m.timestamp WHERE d.tagname=simulation00001
AND d.timestamp>='30-nov-2001 00:00:00'
AND d.timestamp<='06-dec-2001 00:00:00'

Example 8: Retrieve the Greatest Values Across All Simulation Tags

In the following example, we join the ihRawData and ihTags tables, because the ihRawData table does not contain the CollectorType column.

SELECT TOP 300 ihRawData.tagname, ihRawData.timestamp, ihRawData.value, ihRawData.Quality FROM ihRawData
INNER JOIN ihTags ON ihRawdata.Tagname = ihTags.Tagname
WHERE ihRawData.tagname LIKE simulation*
AND ihRawData.timestamp>=11/28/2001
AND ihRawData.timestamp<=11/29/2001
AND ihRawData.samplingmode=interpolated AND ihRawData.intervalmilliseconds=1H
AND ihTags.datatype!=FixedString
AND ihTags.datatype!=variablestring
AND ihRawData.quality>0
ORDER BY value DESC, timestamp DESC

Example 9: Join the ihComments and ihRawData Tables

SET starttime='28-nov-2001 08:00', endtime='29-nov-2001 09:00', samplingmode=interpolated, intervalmilliseconds=6m
SELECT d.tagname, d.timestamp, d.value, c.storedontimestamp, c.username, c.datatypehint, c.comment FROM ihcomments c
FULL OUTER JOIN ihrawdata d ON c.tagname=d.tagname
AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*'
ORDER BY d.tagname, d.timestamp,c.storedontimestamp, c.datatypehint, c.username, c.comment

Example 10: Report by Tag Description

In the following example, we join the ihRawData and ihTags tables to get the Description column from the ihTags table.

SELECT d.timestamp, t.description, d.value, d.quality
FROM ihrawdata d INNER JOIN ihtags t ON d.tagname=t.tagname
WHERE d.tagname LIKE '*0001' ORDER BY d.timestamp, t.description

Example 11: Join Three Tables

SELECT ihTags.Tagname, ihTags.Description, ihRawData.TimeStamp, ihRawData.Value, ihRawData.SamplingMode, ihComments.Comment
FROM ihTags ihTags, ihRawData ihRawData, ihComments ihComments
WHERE ihTags.Tagname = ihRawData.Tagname
AND ihRawData.Tagname = ihComments.Tagname
AND ihRawData.Timestamp = ihComments.Timestamp
AND ihRawData.TimeStamp >= {ts '2002-03-01 09:39:00.000'}
AND ihRawData.TimeStamp <= {ts '2002-03-01 09:41:00.000'}
AND ihRawData.SamplingMode = 'RawByTime'
AND ihTags.Tagname LIKE '%TestTag1%'

Example 12: Perform a Right Join (Older Syntax)

SELECT ihTags.Tagname, ihTags.CollectionInterval, ihCollectors.CollectorName, ihCollectors.DefaultCollectionInterval
FROM ihTzzz|

Example 13: Perform a Left Join (Older Syntax)

SELECT ihTags.Tagname, ihTags.CollectionInterval, ihCollectors.CollectorName, ihCollectors.DefaultCollectionInterval
FROM ihTags ihTags, ihCollectors ihCollectors
WHERE
ihTags.CollectionInterval *=ihCollectors.DefaultCollectionInterval
AND ihTags.Tagname LIKE '%TestTag%'