How to join multiple tables and printing the result for same id which are populating more than one i-CodePudding

CREATE TABLE details_1 (
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    CONSTRAINT pk_details_1_e_id PRIMARY KEY ( e_id )
);

insert into details_1 values(11,'A');


CREATE TABLE ques_ans (
    ques_ans_id  NUMBER(10),
    ref_ques_id  NUMBER(10),
    ref_ans_id   NUMBER(10),
    e_id         NUMBER(10),
    CONSTRAINT pk_ques_ans PRIMARY KEY ( ques_ans_id ),
    CONSTRAINT fk_ques_ans FOREIGN KEY ( e_id )
        REFERENCES details_1 ( e_id ),
        constraint fk_ques_and_ques_id foreign key(ref_ques_id)
        references ques_ref (ques_id)
);

insert into ques_ans values(1,3,1,11);
insert into ques_ans values(2,2,2,11);
insert into ques_ans values(3,4,1,11);

CREATE TABLE ques_ref (
    ques_id     NUMBER(10),
    code        VARCHAR2(50),
    code_label  VARCHAR2(100),
    constraint pk_ques_ref primary key(ques_id)
);

insert into ques_ref values(3,'changes_exist','Any known changes');
insert into ques_ref values(2,'E_Clubbed','E_id clubbed with other');
insert into ques_ref values(4,'E_impacted','E impacted by other');

CREATE TABLE ans_ref (
    ref_ans_id  NUMBER(10),
    code        VARCHAR2(10),
    code_value  VARCHAR2(30)
);

insert into ans_ref values(1,'R_Yes','Yes');
insert into ans_ref values(2,'R_No','No');

The problem facing in joining the tables :

Table ques_ans has ref_ques_id column that is being populated from ques_ref table. So, If ref_ques_id = 3 and ref_ans_id = 1 then it should display 'Yes' i.e populating from ans_ref table. Likewise for ref_ques_id = 2 then it should display 'No' and same for ref_ques_id = 4.

My Attempt :

select d.e_id, qa.ref_ques_id,
ar.code_value

from details_1 d
join ques_ans qa on(d.e_id = qa.e_id)
join ans_ref ar on(ar.ref_ans_id = qa.ref_ans_id) ;

In my attempt, I am getting 3 rows but ideally expected output should be like attached in the screenshot.

Columne e_id : Coming from details_1 table

Column Changes_exist: Validation in the ques_ans table ref_ques_id column and based on the ref_ans_id printing Yes or no.

Column E_clubbed: Validation in the ques_ans table ref_ques_id column and based on the ref_ans_id printing Yes or no.

Column E_Impacted: Validation in the ques_ans table ref_ques_id column and based on the ref_ans_id printing Yes or no.

The output should be like the attached screenshot but I got stuck that how this can be printed in a single row

Tool: SQL Developer Version: 20.4

CodePudding user response：

Code you wrote almost does it; there's another join missing (with ques_ref) so that you'd extract code name, but that still doesn't "fix" it because - as far as I can tell - there's no way to do it "dynamically" within SQL. What we usually do is to pivot data. One option is to use an aggregate function (max in this example) along with case expression, and to literally name every column (using code name you've previously extracted, but just to be of some help while writing column's alias).

SQL> with temp as
  2    (select d.e_id, qa.ref_ques_id, qr.code, ar.code_value
  3     from details_1 d
  4     join ques_ans qa on d.e_id = qa.e_id
  5     join ans_ref ar on ar.ref_ans_id = qa.ref_ans_id
  6     join ques_ref qr on qr.ques_id = qa.ref_ques_id
  7    )
  8  select e_id,
  9    max(case when ref_ques_id = 3 then code_value end) changes_exist,
 10    max(case when ref_ques_id = 2 then code_value end) e_clubbed,
 11    max(case when ref_ques_id = 4 then code_value end) e_impacted
 12  from temp
 13  group by e_id;

      E_ID CHANGES_EXIST   E_CLUBBED       E_IMPACTED
---------- --------------- --------------- ---------------
        11 Yes             No              Yes

SQL>