8 Replies Latest reply: Nov 18, 2012 11:32 PM by user10566312 RSS

    Help needed with JOIN condition

    user10566312
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Yes it is fine. Works like magic. Thanks.