3 Replies Latest reply on Apr 18, 2008 10:14 PM by palkema

    Query Issue...

    palkema
      Hey all, I am having an issue here and i was hoping that somone could help.

      I am trying to retrieve an outputted list of all results that don't equal eachother.

      ie, I have two databases and i want to view what images are in one database that are not in the other database. Is there a way to do this?

      This is what i am doing now:

      <cfquery name="imagelist1" datasource="database1">
      SELECT filename, imagename
      FROM iod_product_images
      </cfquery>


      <cfquery name="imagelist2" datasource="database2">
      SELECT filename, imagename
      FROM iod_product_images
      </cfquery>


      <cfquery dbtype="query" name="final">
      SELECT filename FROM imagelist1
      WHERE imagelist1.filename = imagelist2.filename
      </cfquery>




      <cfoutput query="final">
      <cfset count = '#count + 1#'>
      </cfoutput>

      <cfoutput>
      #count#
      </cfoutput>
        • 1. Re: Query Issue...
          MichaelSJudd Level 1
          If you don't mind me asking, what is the issue you are having? Are you getting errors?

          Anyway, I did notice in your count that it is not already set to 0. Personally I do the same thing, but like this:
          <cfset count = 0>
          <cfoutput query="final">
          <cfset count = count+1>
          </cfoutput>

          If there is some other problem, can you be a bit more specific, and I'm sure someone can help out.

          - Mike
          • 2. Re: Query Issue...
            Dan Bracuk Level 5
            In some types of dbs, you can select from more than one db if you are properly set up. If not, you can do something like this.

            query1
            select somefield
            from sometable etc

            query2.
            select whatever
            from wherever
            where somefield not in (the list from the first query)

            • 3. Re: Query Issue...
              palkema Level 1
              Hey Guys,
              I apologize for my poorly written question, I had a conference that i was supposed to attend and i was in the middle of writing the previous post when i realized that i was going to be late.

              Sorry i did not explain myself well. :)

              Anyway Dan Bracuk didn't actually solve the issue that i was having but he made me realize that what I was in error was in my sql statement. I had been pulling pulling, "* WHERE imagelist1.filename <> imagelist2.filename"

              When i should have been using the NOT EXISTS sql function instead of the <>.

              Thanks guys you rock!