1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Recursive query in oracle to get all associations

Discussion in 'SQL PL/SQL' started by Avirup Sen, Jan 7, 2017.

  1. Avirup Sen

    Avirup Sen Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Hi,
    I need help of forming a SQL query for the below situation.

    There's a table (T_BATCH_RULE) having 2 VARCHAR2(255 char) columns [BATCH_NAME, RULE_NAME]

    Assume the following data

    BATCH_NAME RULE_NAME
    B1 R1
    B1 R2
    B1 R3
    B2 R3

    Since R3 is associated to both Batch B1 and B2, I want all the rules associated with B1 to be listed as part of B2 as well.

    My output should look something like below

    BATCH_NAME RULE_NAME
    B1 R1
    B1 R2
    B1 R3
    B2 R1
    B2 R2
    B2 R3

    Please help me with the SQL query (No PL/SQL please)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    735
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    example 1:
    Code (SQL):
    WITH b_rule (BATCH_NAME ,RULE_NAME) AS
    (SELECT 'B1', 'R1' FROM dual UNION ALL
    SELECT 'B1', 'R2' FROM dual UNION ALL
    SELECT 'B1', 'R3' FROM dual UNION ALL
    SELECT 'B2' ,'R3' FROM dual
    ),
    list_batch AS
    (SELECT DISTINCT batch_name FROM b_rule ),
    list_rules AS
    (
    SELECT DISTINCT rule_name FROM b_rule )

    SELECT l1.batch_name,l2.rule_name FROM list_batch l1
    CROSS JOIN list_rules l2 ;
     
  3. Avirup Sen

    Avirup Sen Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Cross join won't work over here
    Example, when you have B3 and R4, then R4 will get associated to B1 and B2 as well!!

    WITH b_rule (BATCH_NAME ,RULE_NAME) AS
    (SELECT 'B1', 'R1' FROM dual UNION ALL
    SELECT 'B1', 'R2' FROM dual UNION ALL
    SELECT 'B1', 'R3' FROM dual UNION ALL
    SELECT 'B2' ,'R3' FROM dual union all
    SELECT 'B3' ,'R4' FROM dual
    ),
    list_batch AS
    (SELECT DISTINCT batch_name FROM b_rule ),
    list_rules AS
    (
    SELECT DISTINCT rule_name FROM b_rule )

    SELECT l1.batch_name,l2.rule_name FROM list_batch l1
    CROSS JOIN list_rules l2 ;

     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    735
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Perhaps....
    For the decision of the task in a general view it is necessary to formulate more precisely the rule.....
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,533
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    Code (SQL):
    WITH b_rule (BATCH_NAME ,RULE_NAME) AS
    (SELECT 'B1', 'R1' FROM dual UNION ALL
    SELECT 'B1', 'R2' FROM dual UNION ALL
    SELECT 'B1', 'R3' FROM dual UNION ALL
    SELECT 'B2' ,'R3' FROM dual UNION ALL
    SELECT 'B3' ,'R4' FROM dual
    ),
    list_batch AS
    (SELECT DISTINCT batch_name FROM b_rule ),
    list_rules AS
    (
    SELECT DISTINCT rule_name FROM b_rule )
    SELECT l1.batch_name,l2.rule_name FROM list_batch l1
    CROSS JOIN list_rules l2
    WHERE l2.rule_name IN
    (SELECT rule_name FROM b_rule WHERE batch_nane = 'B1');
     
    Unfortunately I am without a database to test this code.
     
  6. nikhil9421

    nikhil9421 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    100
    Check This query.. This will definitely help you :)

    select distinct a.batch,b.rule from testbr a, testbr b
    where a.rule in (select rule from testbr where batch=b.batch)
    order by 1,2;
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,533
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it won't as rows can be added to the table that don't meet the initial criteria (the Rule must be included in the list of B1 associated rules [R1,R2,R3] and the batch must be either B1 or B2). Adding:

    B2 R4
    B3 R4
    B3 R5
    B4 R6
    ...

    violates the criteria and those rows will be returned by your query in addition to the specified output values. Likewise adding any new batch number will return that new batch number and its associated rule. You need to re-think your 'solution'.