如何在SQL中显示外国钥匙值，而不是其ID-S

Question

我在制作日历时遇到了问题。我有一个名为rezervacije（预订）的表格，带有name，start date，结束日期，等。 ;还有recervation_type_fk，将其连接到另一张表格的外键，其中保留类型是用名称，价格等列写下的。

现在我的问题是：

我如何显示保留名称类型而不是ID-S？ 我当前的SQL查询是这样的：

select 
    ID_REZ, /*ID of the reservation, number goes up by 1 on every insert automatically generated*/
    START_REZ, /*DATE OF THE START OF THE RESERVATION*/
    END_REZ, /*DATE OF THE END OF THE RESERVATION*/
    DETAILS_REZ, /*VARCHAR2 - comments on the reservation*/
    case 
        when DETAILS_REZ is not null 
        then title || '[' || QUANTITY_REZ || 'x ' || /*HERE I WANT TO DISPLAY THE FK NAME*/ 
             || ' hours on ' || NUMBER_OF_HOURS || ' - ' || DETAILS_REZ || ' ]'
    end as title,
    QUANTITY_REZ, /* NUMBER Quantity of things on the reservation */
    NUMBER_OF_HOURS, /*NUMBER of hours of the reservation calculated automatically*/
    case
        when REZ_TYPE_FK = 8
        then  'apex-cal-red' /*THIS FK is a number 
Identity: always, in increments of 1 that connects to another table that has a name price etc and is saved in my RESERVATION table as a foreign key and I don't want to display it as a number but as a name of the RESERVATION TYPE*/
        when REZ_TYPE_FK = 9 then  'apex-cal-orange'
        when REZ_TYPE_FK = 10  then  'apex-cal-blue'
        when REZ_TYPE_FK = 11 then 'apex-cal-green'
        when REZ_TYPE_FK = 12 then 'apex-cal-lime'
    end as CSS_REZ   /*Varchar2 that paints my calendar entries to a certain color */
from
    REZERVACIJE
where 
    (nvl(:REZ_TYPE_OBJ,'0') = '0' or REZ_TYPE_FK = :REZ_TTYPE_OBJ)  /* This is for a LOV selection where i can filter what RESERVATION TYPE i want to see*/
order by
    START_REZ

因此，我只想在标题中显示预订类型的名称。我知道这是一个新秀问题，可能很简单，但是请耐心，因为我才刚刚开始。

Answer 1

带有以下假设：

1. “另一个表”被命名为reservation_types带有主键reservation_type_id以及需要显示的列为reservient_type_type_name
2. 列列rezervacije.rez_type_fk始终具有一个值。

选择将是：

select 
    R.ID_REZ, /*ID of the reservation, number goes up by 1 on every insert automatically generated*/
    R.START_REZ, /*DATE OF THE START OF THE RESERVATION*/
    R.END_REZ, /*DATE OF THE END OF THE RESERVATION*/
    R.DETAILS_REZ, /*VARCHAR2 - comments on the reservation*/
    case 
        when R.DETAILS_REZ is not null 
        then R.title || '[' || R.QUANTITY_REZ || 'x ' || T.RESERVATION_TYPE_NAME
             || ' hours on ' || R.NUMBER_OF_HOURS || ' - ' || R.DETAILS_REZ || ' ]'
    end as title,
    R.QUANTITY_REZ, /* NUMBER Quantity of things on the reservation */
    R.NUMBER_OF_HOURS, /*NUMBER of hours of the reservation calculated automatically*/
    case
        when R.REZ_TYPE_FK = 8
        then  'apex-cal-red' /*THIS FK is a number 
Identity: always, in increments of 1 that connects to another table that has a name price etc and is saved in my RESERVATION table as a foreign key and I don't want to display it as a number but as a name of the RESERVATION TYPE*/
        when R.REZ_TYPE_FK = 9 then  'apex-cal-orange'
        when R.REZ_TYPE_FK = 10  then  'apex-cal-blue'
        when R.REZ_TYPE_FK = 11 then 'apex-cal-green'
        when R.REZ_TYPE_FK = 12 then 'apex-cal-lime'
    end as R.CSS_REZ   /*Varchar2 that paints my calendar entries to a certain color */
from
    REZERVACIJE R
    JOIN RESERVATION_TYPES T ON R.REZ_TYPE_FK = T.RESERVATION_TYPE_ID
where 
    (nvl(:REZ_TYPE_OBJ,'0') = '0' or R.REZ_TYPE_FK = :REZ_TTYPE_OBJ)  /* This is for a LOV selection where i can filter what RESERVATION TYPE i want to see*/
order by
    R.START_REZ

用实际的表/列名称替换假设（1）中的值。如果假设（2）不正确，则您将无法获得具有r.rez_type_fk的null值的记录的行。将更改为 如果要所有记录，请左Out Out Join 。

Answer 2

这是加入最简单的用例

select
    r.*,
    rt.rez_type_name
from
    reservation r
    join
    reservation_type rt on rt.res_type_id = r.res_type_id