CODEWITHSUNDEEP
sqloracle-database
user1205746
user1205746

Reputation: 3364

Combining 2 Oracle Tables

I have 2 tables A and B with the following data: (I use oracle 11g)

enter image description here

enter image description here

I need to combine the 2 tables above into 1 table based on the field "Code". This illustration is just a simplified version of my bigger problem at work. Basically, I have a form with structure stored in B and responses stored in A. However, the table A where the responses are kept does not keep the headers which are stored in B. In the report, I need to print the header along with the response. Anyway, not wanting to complicate the issue, the result I am looking for is in the following format:

enter image description here

Is this feasible to produce the result I wanted using select, join and possible union? I can not make it work. The statement I can come up with so far is:

select * from b left outer join a
on b."Code"= a."Code"

But the result is not what I am looking for. Is this even feasible without creating a procedure to format it. Ideally, it should be put in a view

Below is the script to generate my test data:

  CREATE TABLE "A" 
   (    "Id" NUMBER, 
    "Row" NUMBER, 
    "Description" VARCHAR2(20 BYTE), 
    "Type" NUMBER, 
    "Answer" VARCHAR2(20 BYTE), 
    "Code" VARCHAR2(20 BYTE), 
    "ClientId" NUMBER
   )  ;

Insert into A ("Id","Row","Description","Type","Answer","Code","ClientId") values (1,1,'Question 1',2,'ABC','QCONTROL',1000);
Insert into A ("Id","Row","Description","Type","Answer","Code","ClientId") values (2,3,'Question 2',2,'DEC','QCONTROL',1000);
Insert into A ("Id","Row","Description","Type","Answer","Code","ClientId") values (1,1,'Question 1',2,'XYZ','QCONTROL',2000);
Insert into A ("Id","Row","Description","Type","Answer","Code","ClientId") values (2,3,'Question 2',2,'STU','QCONTROL',2000);
Insert into A ("Id","Row","Description","Type","Answer","Code","ClientId") values (3,1,'Question 5',3,'JKL','QCONTROL',3000);


  CREATE TABLE "B" 
   (    "Id" NUMBER, 
    "Desc" VARCHAR2(20 BYTE), 
    "Row" NUMBER, 
    "Type" NUMBER, 
    "Code" VARCHAR2(20 BYTE)
   )  ;

Insert into B ("Id","Desc","Row","Type","Code") values (10,'----------',2,0,'QCONTROL');
Insert into B ("Id","Desc","Row","Type","Code") values (20,'**********',10,0,'OTHER');

Upvotes: 0

Views: 68

Answers (3)

Sandeep
Sandeep

Reputation: 806

Sorry I am not able to understand your exact requirements but the below mentioned query will return the result as shown in the screenshot. I have renamed the columns like Row to Row1 as I don't like use double quotes in the query.

WITH subqueryfactoring AS
  (SELECT A.id,
    A.row1,
    A.description,
    A.type1,
    A.answer,
    A.code,
    A.clientid
  FROM A
  INNER JOIN B
  ON A.code=B.code

  UNION

  SELECT B.id,
    B.row1,
    B.desc1,
    B.type1,
    NULL,
    B.code,
    A.clientid
  FROM A
  INNER JOIN B
  ON A.code=b.code
  )
SELECT * FROM subqueryfactoring ORDER BY clientid, id nulls last;

Upvotes: 0

Nickolay Komar
Nickolay Komar

Reputation: 340

is this what you want?

with res as(
select "Id" , "Row" , "Description" ,  "Type" , "Answer", "Code", "ClientId" from A

union all

select B."Id" as "Id" , B."Row" as "Row" , B."Desc" as "Description",
 B."Type" as "Type", null as "Answer", B."Code" as "Code", A1."ClientId" as "ClientId"
 from B inner join (select distinct "ClientId", "Code" from A) A1 ON B."Code"= A1."Code"
 ) 
select * from res order by "Code", "ClientId", "Answer"

SqlFiddle Link

Upvotes: 1

Davide Guastalli
Davide Guastalli

Reputation: 46

I'm not sure about syntax (I'm not in front of an oracle instance..), but my script can be useful as a starting point.

Select * from
(Select Id, '9999' as Row, Desc,0 as type, B.Code, A.ClientId
from B, A
where A.Code=B.Code 
union all
Select * from A) ab
order by ab.ClientId, ab.row

I put a '9999' in Row to order it in a correct way. I hope it can helps!

Upvotes: 0

Related Questions