5 Replies Latest reply: May 18, 2014 6:55 AM by Frank Kulash RSS

    Need to perform join

    Shuumail

      Oracle Version     =     PL/SQL Release 11.2.0.2.0 - Production

      Hi Gurus

      Need yours help again and appreciate if some help me out.

      I have the following data,

      Sample Data

      With blt as

        (

          Select 'AD' blt  from dual union all

          Select 'DENTAL' blt  from dual union all

          Select 'HEALTH' blt  from dual

        ),

      setcode as

        (

        Select 'ABC' set_cd , 'AD' blt      from dual union all

        Select 'DEF'        , 'DENTAL'       from dual union all

        Select 'TV'         , 'HEALTH'        from dual union all

        Select 'VCR'        , 'HEALTH'      from dual union all

        Select 'HEALTH'  , 'HEALTH'  from dual

        )

      SELECT SET_CD FROM SETCODE A,

                         BLT B

      WHERE A.BLT=B.BLT;

       

      Existing Query Result

      set_cd

      ABC

      DEF

      TV

      VCR

      HEALTH

      Required Result

      ABC

      DEF

      HEALTH

       

       

       

       

      Rules

      In case of joining if there are 2 or more than 2 results return then join set_id=blt like I did with health record.

       

      In summary, I need only single records against 1 blt and if more than 1 set_id exist against same blt then get the one that blt and set_id are same. Thanks

        • 1. Re: Need to perform join
          Solomon Yakobson

          with blt as (

                       select 'AD' blt  from dual union all

                       select 'DENTAL' blt  from dual union all

                       select 'HEALTH' blt  from dual

                      ),

          setcode as (

                       select 'ABC' set_cd , 'AD' blt      from dual union all

                       select 'DEF'        , 'DENTAL'       from dual union all

                       select 'TV'         , 'HEALTH'        from dual union all

                       select 'VCR'        , 'HEALTH'      from dual union all

                       select 'HEALTH'  , 'HEALTH'  from dual

                      ),

                 t as(

                      SELECT  COUNT(*) OVER(PARTITION BY A.BLT) CNT,

                              SET_CD,

                              A.BLT

                        FROM  SETCODE A,

                              BLT B

                        WHERE A.BLT = B.BLT

                     )

          SELECT  SET_CD

            FROM  t

            WHERE CNT = 1

               OR BLT = SET_CD

          /


          SET_CD
          ------
          ABC
          DEF
          HEALTH

          SQL>

           

          SY.

          • 2. Re: Need to perform join
            Frank Kulash

            Hi,

             

            Here's one way:

            SELECT    CASE  COUNT (*)

                          WHEN  1

                          THEN  MIN (s.set_cd)

                          ELSE  s.blt

                      END           AS set_cd

            FROM      setcode  s

            ,         blt      b

            WHERE     s.blt    = b.blt

            GROUP BY  s.blt

            ;

            What if there are multiple rows with the the same blt, but none of them has set_cd=blt?

            • 3. Re: Need to perform join
              Etbin

              No need to join

               

              With blt as

                (

                  Select 'AD' blt  from dual union all

                  Select 'DENTAL' blt  from dual union all

                  Select 'HEALTH' blt  from dual

                ),

              setcode as

                (

                Select 'ABC' set_cd , 'AD' blt      from dual union all

                Select 'DEF'        , 'DENTAL'       from dual union all

                Select 'TV'         , 'HEALTH'        from dual union all

                Select 'VCR'        , 'HEALTH'      from dual union all

                Select 'HEALTH'  , 'HEALTH'  from dual

                )

              select distinct case when cnt = 1 then set_cd else blt end result

                from (SELECT SET_CD,blt,count(*) over (partition by blt) cnt

                        FROM SETCODE

                     )

               

              RESULT
              DEF
              ABC
              HEALTH

               

              Regards

               

              Etbin

              • 4. Re: Need to perform join
                Shuumail

                Thanks for your replies. I created the following query and it is working fine and I will check yours query later. I use the following logic to create query but I'm not satisfy with my query with performance point of view. Please advice.

                SELECT case when exists

                                  (

                                  select count(*)

                                  from setcode a

                                  where a.blt=b.blt

                                   having  count(*)=1

                                  ) then

                                      (

                                      Select set_cd

                                      from setcode cc

                                      where cc.blt=b.blt

                                       )

                              when exists

                                  (

                                  select count(*)

                                  from setcode a

                                  where a.blt=b.blt

                                  having  count(*)>1

                                  ) then

                                     (

                                      Select set_cd

                                      from setcode cc

                                      where cc.set_cd=b.blt

                                       )

                        end set_cd

                FROM     BLT B;

                • 5. Re: Need to perform join
                  Frank Kulash

                  Hi,

                   

                  You don't need all those sub-queries.  Depending on your requirements (in particular, what to do when there are several matching rows, but no row where set_cd=blt) you might need 1 sub-query, to compute analytic functions, but each table name should appear only once, in one FROM clause, and you only need to make 1 pass through each table.  (In the query you posted above, setcode is named 4 times, in 4 different FROM clauses.)  Any of the 3 solutions posted already will be faster than that.

                   

                   

                  Is setcode.blt a foreign key, referencing blt.blt?  If so, you don't blt in this query.  Eliminating the join will improve performance.