Forum Stats

  • 3,827,788 Users
  • 2,260,821 Discussions
  • 7,897,376 Comments

Discussions

Not In Query on select List

NewApexCoder
NewApexCoder Member Posts: 256
edited Oct 25, 2013 2:30PM in APEX Discussions

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)

Tagged:

Best Answer

Answers

This discussion has been closed.