CODEWITHSUNDEEP
sqloracle-database
Durairaj s
Durairaj s

Reputation: 193

Need to populate value into a row using INNER JOIN

I have stored contact in one table and their relationship in one table and child details in another. I need to populate "child name" of each parent using their childid ? 

 CREATE TABLE contact_table(
 contactid NUMBER(3)
,contactname VARCHAR2(30)
,contactage NUMBER(2)
);


CREATE TABLE contact_maritalstatus(
statusid NUMBER(3)
,contactid NUMBER(3)
,contactgender VARCHAR2(8)
,contactstatus VARCHAR2(10)
);
CREATE TABLE contact_relation(
relationid NUMBER(3)
,contactid NUMBER(3)
,partnerid NUMBER(3)
,childid NUMBER(3)
);
CREATE TABLE contact_child(
childid NUMBER(3)
,contactid NUMBER(3)
);

SELECT cb.contactname,cm.contactgender,cm.contactstatus,cr.childid
FROM contact_table cb 
 inner join contact_maritalstatus cm  on cb.contactid = cm.contactid
 inner join contact_relation cr on cb.contactid = cr.contactid;

enter image description here

Upvotes: 0

Views: 51

Answers (1)

APC
APC

Reputation: 146239

You haven't provided the foreign keys so we have to infer your data model. However it seems parents and children are both in contact_table, and the contact_relation joins spouses to each other and parents to children. If so, you simply need join to contact_table a second time, using table and column aliases to distinguish the two instances: 

SELECT cp.contactname as parent_name 
       , cm.contactgender
       , cm.contactstatus
       , cr.childid
       , cc.contactname as child_name 
FROM contact_table cp
 inner join contact_maritalstatus cm  on cp.contactid = cm.contactid
 inner join contact_relation cr on cb.contactid = cr.contactid
 inner join contact_table cc on cc.contactid = cr.childid

Upvotes: 3

Related Questions