1 Reply Latest reply on Jun 9, 2015 1:17 AM by forrestj

    Cannot use a nested query and JOIN in the same SWQL Query

    forrestj

      Hey all,

       

      Solarwinds support have advised they cannot help me with this and have refereed me to the forums.

       

      I am having issues with a nested query and JOIN to a second table. See the below SWQL, It throws an error, "Object reference not set to an instance of an object", I can run the same query direct on the database and it will work.

       

      SELECT CS.Name, C.Name

      FROM Orion.ContainerMembers CS

      INNER JOIN Orion.Container C

      ON C.ContainerID = CS.ContainerID

      WHERE CS.Name IN

      (

        SELECT  ContainerMembers.Name

        FROM Orion.ContainerMembers

        group by ContainerMembers.Name

        having count(ContainerMembers.Name) > 1

      )

      ORDER BY CS.Name


      I note someone else had a similar issue here: Problem with SWQL query


      Any ideas on how I could get something similar working in SWQL, I was hoping to writing some custom queries in the orion interface using this approach.

        • Re: Cannot use a nested query and JOIN in the same SWQL Query
          forrestj

          OK so it turns out I can achieve a similar result by joining to the subquery. See below:

           

          SELECT CS.Name, C.Name

          FROM Orion.ContainerMembers CS

          INNER JOIN

          (

            SELECT  ContainerMembers.Name

            FROM Orion.ContainerMembers

            group by ContainerMembers.Name

            having count(ContainerMembers.Name) > 1

          ) S ON S.Name = CS.Name

          INNER JOIN Orion.Container C

          ON C.ContainerID = CS.ContainerID

          ORDER BY CS.Name

           

          This gives me all objects which are port of multiple groups.