Oracle APEX - SQL Return Display Only Value based on Select List - Beginner SQL Question

Question

I have an application I've made in Oracle APEX, all the newest versions of APEX:

Database 18c+ APEX Version - 20+

Issue: I have a form that I have users select a certain Product from. When they choose that Product I want the Manager for that Portfolio it's related to, to be populated as a display only value.

Table Structure:

Portfolios
   ID
   Portfolio_Name vc
   Manager vc
   column
   column
   column
     Products - this is the child table to Portfolio
        ID
        Product_Name vc
        Portfolio_ID - FK to Portfolio table
        column
        column
        column

Reviews
  ID
  Product_ID - fk to Products
  Product_Name vc
  Manager vc
  column
  column

For the select list on my form where I have the Product Names listed I have the standard:

SELECT PRODUCT_NAME as D, PRODUCT_NAME as R
FROM PRODUCTS

I'm wondering if I need to return the ID of the PRODUCT_NAME to make a join work.

I am new at SQL and just stuck on what is probably a pretty easy solution.

I have tried most Joins including Self Joins, Cross Joins and Left Outer Joins.

I expect to pass my page item as the filter. Such as :P13_PRODUCT_NAME = SOMETHING that way when tehy select a certain product the manager appears.

Answer 1

Generally speaking, list of values' query returns two values: display and return, respecitvely. Display value is usually name, return value is usually ID. Therefore, your "product" LoV query would be

select product_name as display_value,
       id           as return_value
from products

Item name would be P13_PRODUCT_ID, but you'd set label to e.g. "Product" and users would see display values, i.e. product names. If you pass product from some other page, you'd also pass its ID, not its name. Two products can have the same name (but differ on other attributes), so - using names in joins is not a very good idea. Use IDs.

When referencing that item, you should do that on ID which is usually a primary key column and is, therefore, indexed which improves performance. It means that query might look like this:

select f.portfolio_name,
       r.id      as review_id,
       r.manager
from portfolios f join products p on p.portfolio_id = f.id
                  join reviews  r on r.product_id   = p.id
where p.id = :P13_PRODUCT_ID