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
Advertisement
Answer
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>