title: Azure Stream Analytics query with multiple inputs
body: <h1>Issue</h1>
  <p>I have a Azure IoT-Hub sending messages to a Azure Stream Analytics Job.</p>
  <p>Each message contains a 'NodeName'.
  I have a table 'plcnext_nodes' which has a unique 'NodeId' for each node with their corresponding 'NodeName'.</p>
  <p>How can I use both the input from the 'plcnext_nodes' table and the IoT-Hub messages to store the event data in another SQL table using the 'NodeId'?</p>
  <p>I want to use the 'NodeId' instead of the 'NodeName' because some names can get very long and saving them over and over with each message is a waste of storage.</p>
  <hr />
  <h1>Desired solution</h1>
  <p>I would like to parse the following <strong>message from the IoT Hub</strong>:</p>
  <pre><code>{
      &quot;NodeName&quot;: &quot;ns=5;s=Arp.Plc.Eclr/DI2&quot;,
      &quot;NodeDataType&quot;: &quot;Boolean&quot;,
      &quot;EventValue&quot;: 0,
      &quot;EventMeasuredUtcTime&quot;: &quot;2019-11-11T12:15:22.4830000Z&quot;,
      &quot;EventProcessedUtcTime&quot;: &quot;2019-11-11T12:41:57.1706596Z&quot;,
      &quot;EventEnqueuedUtcTime&quot;: &quot;2019-11-11T12:15:32.1260000Z&quot;,
      &quot;IoTHub&quot;: {
          ...
      }
  }
  </code></pre>
  <p>Compare the 'NodeName' with those in the <strong>plcnext_nodes</strong> table to get the appropriate 'NodeId':</p>
  <pre><code>NodeId  NodeName                 NodeDataType
  ---------------------------------------------
  1       ns=5;s=Arp.Plc.Eclr/DI1  Boolean
  2       ns=5;s=Arp.Plc.Eclr/DI2  Boolean
  ...
  </code></pre>
  <p>To get the following output and insert into the <strong>plcnext_events</strong> table:</p>
  <pre><code>NodeId  EventValue  EventMeasured
  -----------------------------------------------
  1       0           2019-11-11 12:15:22.4830000
  </code></pre>
  <hr />
  <h1>Query</h1>
  <p>I have tried the following query on Azure Stream Analytics:</p>
  <pre><code>SELECT
      NodeId,
      EventValue,
      EventMeasuredUtcTime,
      EventEnqueuedUtcTime,
      EventProcessedUtcTime
  INTO
      [plcnext_events]
  FROM
      [plcnext_nodes],
      [iot_hub]
  WHERE
      [iot_hub].NodeName = [plcnext-nodes].NodeName
  </code></pre>
  <p>But JOIN is not supported in FROM and I haven't been able to use a JOIN clause because of it's DATEDIFF restriction (The plcnext_nodes table has no timestamps)</p>
  <h3>Is there a way to achieve this?</h3>