This discussion is archived
8 Replies Latest reply: Nov 18, 2012 9:23 PM by jeneesh RSS

Help needed with JOIN condition

user10566312 Newbie
Currently Being Moderated
I have two tables tb1 and tb2. They join on two columns, viz., plan_name and activity_name. However, I have to ensure the following conditions while joining:

1. Pick up all the records that join between tb1 and tb2.

2. If tb1.plan_name is 'Adhoc Activity' then pick the rows which join with tb2. Also pick up those rows which don't join with tb2 where plan_name = 'Adhoc Activity'. For example:
tb1 has 2 records with plan_name Adhoc Acitivity
plan_name activity_name
Adhoc Activity A1
Adhoc Activity A2
tb2 has 1 record with plan_name Adhoc Activity
plan_name activity_name
Adhoc Activity A1

then both records from tb1 should be taken. besides other joining records where plan_name <> 'Adhoc Acitivity' should also be taken
  • 1. Re: Help needed with JOIN condition
    jeneesh Guru
    Currently Being Moderated
    Assuming the joining column will be not null
    select *
    from(
       select a.plan_name a_plan_name,b.plan_name b_plan_name,....
       from tb1 a,tb2 b
       where a.plan_name = b.plan_name(+)
       and a.activity_name = b.activity_name(+)
         )
    where (a_plan_name = 'Adhoc Activity'  or b_plan_name is not null)
  • 2. Re: Help needed with JOIN condition
    Manik Expert
    Currently Being Moderated
    No test data to check my queries.. Please follow the instructions of the forum for asking questions.

    I have two possible sql queries for the requirement you posted.

    1) Query 1 : Which should also include the point 2 in your description.
    SELECT *
      FROM    tb1 a
           LEFT OUTER JOIN
              tb2 b
           ON (a.plan_name = b.plan_name AND a.activity_name = b.activity_name);
    2) Query 2: Should be same as that of Query1:

    I still wanted to see if this query returns same data as of the query 1
    SELECT *
      FROM    tb1 a
           JOIN
              tb2 b
           ON (a.plan_name = b.plan_name AND a.activity_name = b.activity_name)
    UNION
    SELECT *
      FROM    (SELECT *
                 FROM tb1
                WHERE UPPER (plan_name) LIKE 'ADHOC ACTIVITY') a
           LEFT OUTER JOIN
              (SELECT *
                 FROM tb2
                WHERE UPPER (plan_name) LIKE 'ADHOC ACTIVITY') b
           ON (a.plan_name = b.plan_name AND a.activity_name = b.activity_name);
    Cheers,
    Manik.
  • 3. Re: Help needed with JOIN condition
    user10566312 Newbie
    Currently Being Moderated
    Can you reqrite query two using some OR condition instead of UNION ALL. Reason being this is a part of a long query and it would be possible to add a union all at this point.
  • 4. Re: Help needed with JOIN condition
    jeneesh Guru
    Currently Being Moderated
    user10566312 wrote:
    Can you reqrite query two using some OR condition instead of UNION ALL. Reason being this is a part of a long query and it would be possible to add a union all at this point.
    Did you check what I posted?

    Or I misunderstood..?
  • 5. Re: Help needed with JOIN condition
    jeneesh Guru
    Currently Being Moderated
    with tb1 as
    (
    select 'Adhoc Activity' plan_name,'A1' activity_name from dual union all
    select 'Adhoc Activity' plan_name,'A2' activity_name from dual union all
    select 'another Activity' plan_name,'A2' activity_name from  dual union all
    select 'Some another Activity' plan_name,'A2' activity_name from dual 
    ),
    tb2 as
    (
    select 'Adhoc Activity' plan_name,'A1' activity_name from  dual union all
    --select 'Adhoc Activity' plan_name,'A2' activity_name dual union all
    select 'another Activity' plan_name,'A2' activity_name from  dual --union all
    --select 'Some another Activity' plan_name,'A2' activity_name dual 
    )
    select *
    from(
       select a.plan_name a_plan_name,b.plan_name b_plan_name
       from tb1 a,tb2 b
       where a.plan_name = b.plan_name(+)
       and a.activity_name = b.activity_name(+)
         )
    where (a_plan_name = 'Adhoc Activity'  or b_plan_name is not null);
    
    A_PLAN_NAME           B_PLAN_NAME    
    --------------------- ----------------
    Adhoc Activity        Adhoc Activity   
    another Activity      another Activity 
    Adhoc Activity                         
    Edited by: jeneesh on Nov 19, 2012 10:27 AM
  • 6. Re: Help needed with JOIN condition
    user10566312 Newbie
    Currently Being Moderated
    I think the first condition fails. If plan_name is not 'Adhoc Activity' but if it is present in both tb1 and tb2 then it should be picked... like inner join.
  • 7. Re: Help needed with JOIN condition
    jeneesh Guru
    Currently Being Moderated
    user10566312 wrote:
    I think the first condition fails. If plan_name is not 'Adhoc Activity' but if it is present in both tb1 and tb2 then it should be picked... like inner join.
    ???

    Could you tell, what is the expected output for below data ? Is it not the one what I am getting -"Some Another Activity" is not picked..?
    with tb1 as
    (
    select 'Adhoc Activity' plan_name,'A1' activity_name from dual union all
    select 'Adhoc Activity' plan_name,'A2' activity_name from dual union all
    select 'another Activity' plan_name,'A2' activity_name from  dual union all
    select 'Some another Activity' plan_name,'A2' activity_name from dual 
    ),
    tb2 as
    (
    select 'Adhoc Activity' plan_name,'A1' activity_name from  dual union all
    select 'another Activity' plan_name,'A2' activity_name from  dual --union all
    )
    select *
    from(
       select a.plan_name a_plan_name,b.plan_name b_plan_name,a.activity_name a_act,
              b.activity_name b_act
       from tb1 a,tb2 b
       where a.plan_name = b.plan_name(+)
       and a.activity_name = b.activity_name(+)
         )
    where (a_plan_name = 'Adhoc Activity'  or b_plan_name is not null)
    order by 1;
    
    A_PLAN_NAME           B_PLAN_NAME      A_ACT B_ACT
    --------------------- ---------------- ----- -----
    Adhoc Activity        Adhoc Activity   A1    A1    
    Adhoc Activity                         A2          
    another Activity      another Activity A2    A2    
  • 8. Re: Help needed with JOIN condition
    user10566312 Newbie
    Currently Being Moderated
    Yes it is fine. Works like magic. Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points