4 Replies Latest reply: Oct 25, 2013 1:30 PM by NewApexCoder RSS

    Not In Query on select List

    NewApexCoder

      Apex 4.2

       

      Two Tables

      SITES

        Site_id (pk)

        Site_name

        Date

       

      ACTIVATION

         Activation_id (pk)

         Activation_date

         Deactivation_date

         Site_id (fk)

       

      * I have a report based on the ACTIVATION table. Upon clicking an edit link on any row of the report, it takes you to a form to edit the information. The form allows you to edit the Site_id. The Site_id is represented as a select list with the following LOV query:

       

      SELECT SITE_NAME, SITE_ID

      FROM SITES

       

      * This select list gives me all of the site_names, however, I want to filter this select list by only showing those sites that are not in the report. All of the site names are as follows:

       

      Maryland

      Los Angeles

      Natchez

      Bakersfield

      Austin

       

      The report has rows:

       

      Activation ID     Site Name              Activation Date     Deactivation Date

        1                         Natchez                    3/11/2013         

        2                         Los Angeles               5/27/2020

       

       

      I would like for the list to only display the ones that are not in the report, i.e. Austin, Bakersfield, Maryland.

       

      I have tried to write a NOT IN query. When I click an edit link from the report to navigate to the form, the select list shows the list of entries that I desire, however, the row that I clicked shows the id of the Site Name instead of the actual name. I'm not sure where my error is. I hope all this makes sense. Thanks for your help in advance. Oh, and my query that I wrote is as follows:

       

      SELECT SITE_NAME, SITE_ID

      FROM SITES

      WHERE SITE_ID NOT IN (SELECT SITE_ID

      FROM ACTIVATION

      WHERE this = that)