1 2 Previous Next 18 Replies Latest reply: Jan 10, 2013 6:51 AM by IamHariKrishna Go to original post RSS
      • 15. Re: help to write a join condition in oracle
        Bawer
        Harry wrote:
        Bawer, even I changes the condition, the query resulting same number of rows.

        select e.GROUPID,e.GROUPNAME,e.PARENTGROUPID,ew.WORKINGASSETGROUPINGID
        from ENTERPRISEGROUPWAG ew
        left join ENTERPRISEGROUP e on ( e.groupid = ew.groupid and e.activeflg = 1)
        WHERE ew.INACTIVEDATE is not null
        and e.groupid is null
        /

        7774 rows selected.

        Elapsed: 00:00:00.35
        this should be expected output.

        your query was
        select count(distinct groupid) from ENTERPRISEGROUPWAG
        where groupid not in (select groupid from ENTERPRISEGROUP where activeflg = 1) and inactivedate is null; 2
        count(distinct groupid) you got only count of differently groupdis. but the query returns all rows with this groupid.

        run it with count(*)

        Edited by: Bawer on 10.01.2013 11:53
        • 16. Re: help to write a join condition in oracle
          IamHariKrishna
          Some more information --


          SQL> select count(GROUPID) from enterprisegroupwag where inactivedate is null;

          COUNT(GROUPID)
          --------------
          387632

          Elapsed: 00:00:00.06
          SQL> select count(GROUPID) from ENTERPRISEGROUP where activeflg = 1;


          COUNT(GROUPID)
          --------------
          2280

          Elapsed: 00:00:00.01
          SQL> select count(*) from ENTERPRISEGROUPWAG
          where groupid not in (select groupid from ENTERPRISEGROUP where activeflg = 1) and inactivedate is null; 2


          COUNT(*)
          ----------
          24646

          Elapsed: 00:00:00.15

          Edited by: Harry on Jan 10, 2013 4:34 PM
          • 17. Re: help to write a join condition in oracle
            Bawer
            Harry wrote:
            Some more information --


            SQL> select count(GROUPID) from enterprisegroupwag where inactivedate is null;

            COUNT(GROUPID)
            --------------
            387632

            Elapsed: 00:00:00.06
            SQL> select count(GROUPID) from ENTERPRISEGROUP where activeflg = 1;


            COUNT(GROUPID)
            --------------
            2280

            Elapsed: 00:00:00.01
            SQL> select count(*) from ENTERPRISEGROUPWAG
            where groupid not in (select groupid from ENTERPRISEGROUP where activeflg = 1) and inactivedate is null; 2


            COUNT(*)
            ----------
            24646

            Elapsed: 00:00:00.15

            Edited by: Harry on Jan 10, 2013 4:34 PM
            sorry I missed your reply:
            Harry wrote:
            Thanks Bawer, GROUPID is common in both tables, but can have duplicates.
            you should have a unique condition for every row in main table, which can return max one row in joined table.
            In this case you can get more rows because of duplicates. A row in main table can be joined with a row in second table which has same goupid activeflg != 1 (but an another row in second table can have same goupid and activeflg = 1) . In this case it is very confused to use join (than you need to use aggregate functions to identifiy the duplicates returned by join).
            • 18. Re: help to write a join condition in oracle
              IamHariKrishna
              Thank you for help Bawer. Really helpful to me.

              Because of duplicates, the query returning more records than expected. I will go the way you guided (aggr fun to filter dups) and posted final version of query asap.

              Thanks a lot :-)
              1 2 Previous Next