Forum Stats

  • 3,780,922 Users
  • 2,254,456 Discussions
  • 7,879,494 Comments

Discussions

Query required for

VISHWAKARMAPARDEEP
VISHWAKARMAPARDEEP Member Posts: 87

Hi Experts,

I have 2 tables and i want to join both tables and display most of the columns in a single query.

Please guide me to build query.

Details are as follows:-

TABLE 1

CodeG_value
A_valueS_date
A13637151230-07-20 23:00:00
A1373690830-07-20 23:00:00
A13832651030-07-20 23:00:00

TABLE 2

CodeC_descC_valueS_date
A136FR75230-07-20 23:00:00
A136IN46430-07-20 23:00:00
A136NA3330-07-20 23:00:00
A137FR80030-07-20 23:00:00
A137IN35030-07-20 23:00:00
A137NA57030-07-20 23:00:00
A138FR78030-07-20 23:00:00
A138IN67030-07-20 23:00:00
A138NA55030-07-20 23:00:00
30-07-20 23:00:00

REQUIRED QUERY TO DISPLAY

CodeG_valueA_valueS_dateFRINNA
A13637151230-07-20 23:00:0075246433
A1373690830-07-20 23:00:00800350570
A13832651030-07-20 23:00:00780670550

Thank you.

Pradeep

VISHWAKARMAPARDEEP

Best Answer

  • FC60
    FC60 Member Posts: 416 Bronze Badge
    edited Aug 1, 2020 8:30PM Accepted Answer

    select T1.CODE

         , T1.G_VALUE

         , T1.A_VALUE

         , FF.C_VALUE as FFR

         , II.C_VALUE as IIN

         , NN.C_VALUE as NNA

      from T1 inner join T2 FF on T1.CODE   = FF.CODE

                                  and

                                  FF.C_DESC = 'FR'

              inner join T2 II on T1.CODE   = II.CODE

                                  and

                                  II.C_DESC = 'IN'

              inner join T2 NN on T1.CODE   = NN.CODE

                                  and

                                  NN.C_DESC = 'NA'

              ;

    VISHWAKARMAPARDEEP

Answers

  • FC60
    FC60 Member Posts: 416 Bronze Badge
    edited Aug 1, 2020 8:30PM Accepted Answer

    select T1.CODE

         , T1.G_VALUE

         , T1.A_VALUE

         , FF.C_VALUE as FFR

         , II.C_VALUE as IIN

         , NN.C_VALUE as NNA

      from T1 inner join T2 FF on T1.CODE   = FF.CODE

                                  and

                                  FF.C_DESC = 'FR'

              inner join T2 II on T1.CODE   = II.CODE

                                  and

                                  II.C_DESC = 'IN'

              inner join T2 NN on T1.CODE   = NN.CODE

                                  and

                                  NN.C_DESC = 'NA'

              ;

    VISHWAKARMAPARDEEP
  • VISHWAKARMAPARDEEP
    VISHWAKARMAPARDEEP Member Posts: 87
    edited Aug 4, 2020 5:06AM

    Thank you for query. It works.

  • Ziut
    Ziut Member Posts: 363 Bronze Badge
    edited Aug 4, 2020 10:11AM

    Hi

    Or...

    with

        function fn_display (v_code t1.code%type, v_c_desc  t2.c_desc%type) return number is

        v_value number;

        begin

          select c_value into v_value

            from t1 join t2  on t1.code = t2.code

            and t2.c_desc = v_c_desc

            and t1.code = v_code;

            return v_value;

        end;

    select distinct code

        , g_value

        , a_value

        , s_date

        , fn_display(code, 'FR') "FR"

        , fn_display(code, 'IN') "IN"

        , fn_display(code, 'NA') "NA"

    from t1

    order by code;

    Regards