OraQA

You go to a wedding reception, and there are a lot of people. All of these people ‘know’ the bride or the groom in some manner. Additionally, a lot of them may know each other in one or more manner. For example, let’s take Ann and David, who meet by the front door. Ann is the bride’s sister. David is the groom’s boss at work. Ann is going out with David’s brother Tom.

So – if you look at the relationships between these two, you have:

Ann goes out with Tom. Tom is a brother of David.
Ann is a sister of Bride. Bride is married to Groom. Groom works for David.

The problem is to write a program that will get the names of two people as input.
It then needs to output a list of all the ways in which these people are related,
much in the same fashion as we showed the list of relationships between Ann and David above.

CREATE TABLE RELATIONSHIP
  ( FROM_NAME VARCHAR2(38 BYTE),
    TO_NAME VARCHAR2(38 BYTE),
    Relation_DESC VARCHAR2(100 BYTE),
    PRIMARY KEY (FROM_NAME, TO_NAME)
  ) organization index;

insert into RELATIONSHIP values (  'bride', 'groom', 'is married to');
insert into RELATIONSHIP values (  'groom', 'bride', 'is married to');
insert into RELATIONSHIP values (  'Ann',   'bride', 'is a sister of');
insert into RELATIONSHIP values (  'bride', 'Ann',   'is a sister of');
insert into RELATIONSHIP values (  'David', 'Tom',   'is a brother of');
insert into RELATIONSHIP values (  'Tom',   'David', 'is a brother of');
insert into RELATIONSHIP values (  'David', 'groom', 'is the boss of');
insert into RELATIONSHIP values (  'groom', 'David', 'works for');
insert into RELATIONSHIP values (  'Ann',   'Tom',   'dates');
insert into RELATIONSHIP values (  'Tom',   'Ann',   'dates');

commit;

COLUMN RELATIONSHIP FORMAT  A68
variable P_FROM varchar2(38)
variable P_TO varchar2(38)
exec :P_FROM:='Ann'
exec :P_TO:='David'

--------------------------------SQL Solution using Recursive Subquery Factoring-------------------------------------

WITH DATA (root, path, TO_NAME) AS
(SELECT FROM_NAME,  FROM_NAME||' '||Relation_DESC||' '||TO_NAME, TO_NAME
 FROM  RELATIONSHIP
 WHERE FROM_NAME  = :P_FROM
 UNION ALL
 SELECT  root, d.path||','||e.FROM_NAME||' '||e.Relation_DESC||' '||e.TO_NAME as path , e.TO_NAME
 FROM  DATA d,  RELATIONSHIP e
 WHERE d.TO_NAME= e.FROM_NAME
 AND e.FROM_NAME != d.root
)
SEARCH DEPTH FIRST BY TO_NAME SET seq
CYCLE TO_NAME SET is_cycle to '1' DEFAULT '0'
SELECT grp, trim(COLUMN_VALUE) relationship
FROM
(SELECT path,  ROWNUM grp
 FROM DATA
 WHERE TO_NAME = :P_TO
 AND is_cycle = 0
 ) a,  xmltable(('"'||replace(a.path, ',', '","')||'"'));

       GRP RELATIONSHIP
---------- --------------------------------------------------------------------
         1 Ann dates Tom
         1 Tom is a brother of David
         2 Ann is a sister of bride
         2 bride is married to groom
         2 groom works for David

SQL>

---------------------------------------------Alternative SQL Solution------------------------------------------------

WITH DATA AS
(SELECT ltrim(sys_connect_by_path(FROM_NAME||' '||Relation_DESC||' '||TO_NAME, ','), ',') as path, ROWNUM grp
 FROM  RELATIONSHIP
 WHERE  TO_NAME  = :P_TO
 START WITH FROM_NAME  = :P_FROM
 CONNECT BY NOCYCLE PRIOR TO_NAME = FROM_NAME
 AND FROM_NAME  != :P_FROM
)
SELECT grp, trim(column_value) AS RELATIONSHIP
FROM DATA a,  xmltable(('"'||replace(a.path, ',', '","')||'"'));

       GRP RELATIONSHIP
---------- --------------------------------------------------------------------
         1 Ann dates Tom
         1 Tom is a brother of David
         2 Ann is a sister of bride
         2 bride is married to groom
         2 groom works for David

SQL>

Filed under General, SQL. Follow any responses to this entry through this RSS 2.0 feed.