I encountered a problem while making a calendar. I have a table named REZERVACIJE (reservations) with columns like name, start date, end date, etc.; and also a reservation_type_FK, a foreign key that connects it to another table where reservation types are written down with columns like name, price, etc.

Now my question is:

How do I show the name of the reservation types instead of the ID-s? my current SQL query is like this:

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

So I just want to show in the title the name of the RESERVATION TYPE from the other table. I know this is such a rookie question and is probably very simple but please have patience because I just started.

CodePudding user response：

This is the most simple use case for JOIN

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

CodePudding user response：

With the following assumptions:

1. "The other table" is named RESERVATION_TYPES with primary key RESERVATION_TYPE_ID and the column that needs to be displayed is RESERVATION_TYPE_NAME
2. The column REZERVACIJE.REZ_TYPE_FK always has a value.

The select would be:

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

Replace the values in assumption (1) with the actual table/column names. If assumption (2) is incorrect you will not get a row for records that have a NULL value for R.REZ_TYPE_FK. Change JOIN to LEFT OUTER JOIN if you want all the records.