3 Replies Latest reply on Sep 15, 2016 10:37 AM by nick_scott

    Software Inventory Query

    nick_scott

      I don't know if something is wrong with my DB or if I forgot how to write a simple query.  I am simply trying to join three tables into one report. For the life of me I cannot make it happen.  The report wizard spins and spins...

       

      select

       

      caption AS Hostname,

      ip_address AS IP,

      site_adderess AS Location,

      Name,

      Version,

      Publisher,

      InstallDate

       

      from

      nodesdata, assetinventory_software, nodescustomproperties

       

      where nodesdata.nodeid = assetinventory_software.nodeid

      OR

      nodesdata.nodeid = nodescustomproperties.nodeid

       

      Can anyone see what I am doing wrong ?

        • Re: Software Inventory Query
          neomatrix1217

          I'm no SQL expert but the query does work I do notice that it takes longer to run using the or statement instead of an and statement

          • Re: Software Inventory Query
            RichardLetts

            You are not joining the tables correctly.

            pedantically you're performing a cross-join between three tables which multiple is the number of rows between the tables and performing a restrict on the result set.

             

            I think this is what you really want to get:

             

            SELECT caption       AS Hostname,

                   ip_address    AS IP,

                   site_adderess AS Location,

                   name,

                   version,

                   publisher,

                   installdate

            FROM   nodesdata

                   LEFT OUTER JOIN assetinventory_software

                                ON nodesdata.nodeid = assetinventory_software.nodeid

                   LEFT OUTER JOIN nodescustomproperties

                                ON nodesdata.nodeid = nodescustomproperties.nodeid

             

            gives you ALL the rows from nodesdata and any matching data from the other two tables

            1 of 1 people found this helpful