10 Replies Latest reply: Oct 21, 2010 2:38 AM by Mac_Freak_Rahul RSS

    SQL Query

    Williams
      Hi Friends,

      I have a table called xxxx and data as follows;

      rownum     w     c     name
      ------------------------------------------------------------
      1     999     (null)     aaa
      2     (null)     222     aaa
      3     911     (null)     bbb
      4     922     (null)     bbb
      5     (null)     111     bbb
      6     933     (null)     ccc
      7     (null)     333     ddd
      8     (null)     444     ddd


      My Required o/p as follows;

      rownum     w     c     name
      --------------------------------------------------------------
      1     999     (null)     aaa
      2     (null)     222     aaa
      3     911     (null)     bbb
      4     922     (null)     bbb
      5     (null)     111     bbb


      Requirement Details are:
      =========================

      I want to display people who has both 'w and c'.... not just c or w...
      from the example, name 'aaa' and bbb has both entries under 'w' and 'c' columns -> want to display these records

      where as name ccc and ddd has either entries.... -> omit these records



      Regards,
      Williams
        • 1. Re: SQL Query
          hm
          -- yourdata:
          with xxxx as
          (
          select 999 w, null c, 'aaa' name from dual union all
          select null, 222, 'aaa' from dual union all
          select 911,null, 'bbb' from dual union all
          select 922, null, 'bbb' from dual union all
          select null, 111, 'bbb' from dual union all
          select 933, null ,'ccc' from dual union all
          select null, 333, 'ddd' from dual union all
          select null, 444, 'ddd' from dual
          )
          -- query:
          select * from xxxx 
          where exists(select w from xxxx x where x.name=xxxx.name and w is not null)
             and exists(select c from xxxx x where x.name=xxxx.name and c is not null)
          • 2. Re: SQL Query
            Saubhik
            SELECT * FROM tbl t1
            WHERE 1= CASE
                      WHEN    (SELECT COUNT(w) FROM tbl t2 WHERE t1.nm=t2.nm) >0
                          AND (SELECT COUNT(c) FROM tbl t2 WHERE t1.nm=t2.nm)  >0           
                      THEN 1
                      ELSE 0
                      END; 
            • 3. Re: SQL Query
              NicloeiW
              this is 2nd time i am posting,
              
               
              SQL> 
              SQL> Create Table t As
                2  Select 1 r, 999 w, Null c,  'aaa' Name From dual Union All
                3  Select 2 ,  Null , 222   ,  'aaa'      From dual Union All
                4  Select 3 ,  911  , Null  ,  'bbb'      From dual Union All
                5  Select 4 ,  922  , Null  ,  'bbb'      From dual Union All
                6  Select 5 ,  Null , 111   ,  'bbb'      From dual Union All
                7  Select 6 ,  933  , Null  ,  'ccc'      From dual Union All
                8  Select 7 , Null  , 333   ,  'ddd'      From dual Union All
                9  Select 8 , Null  , 444   ,  'ddd'      From dual
               10  /
               
              Table created
               
              SQL> 
              SQL> Select r,
                2         w,
                3         c,
                4         Name
                5  From   t
                6  Where  Exists (Select 'X' From t t1 Where t1.name = t.name And c Is Not Null)
                7  And    Exists (Select 'X' From t t2 Where t2.name = t.name And w Is Not Null)
                8  /
               
                       R          W          C NAME
              ---------- ---------- ---------- ----
                       1        999            aaa
                       2                   222 aaa
                       3        911            bbb
                       4        922            bbb
                       5                   111 bbb
               
              SQL> 
              • 4. Re: SQL Query
                KanchDev
                Hi Williams,

                Please try this and let me know.
                select * from xxxx where name in (
                select name from xxxx
                group by name
                having sum(w) >1 and sum(c)>1
                )
                Cheers
                Kanchana
                • 5. Re: SQL Query
                  189821
                  SELECT r, w, c, NAME
                    FROM (SELECT r, w, c, NAME, COUNT(w) OVER(PARTITION BY NAME) AS ct_w,
                                 COUNT(c) OVER(PARTITION BY NAME) AS ct_c
                            FROM xxxx)
                   WHERE ct_w > 0 AND ct_c > 0
                  This will do only one table scan.

                  Urs
                  • 6. Re: SQL Query
                    728534
                    Hi,
                    YOu can do it this way too.
                    with xxxx as
                    (
                    select 999 w, null c, 'aaa' name from dual union all
                    select null, 222, 'aaa' from dual union all
                    select 911,null, 'bbb' from dual union all
                    select 922, null, 'bbb' from dual union all
                    select null, 111, 'bbb' from dual union all
                    select 933, null ,'ccc' from dual union all
                    select null, 333, 'ddd' from dual union all
                    select null, 444, 'ddd' from dual
                    )
                    
                    select name from (
                    select count(w) cnt1,count(c)cnt2,name from xxxx
                    group by name)
                    where cnt1>0 and cnt2 >0
                    Regards,
                    Bhushan
                    • 7. Re: SQL Query
                      Aketi Jyuuzou
                      with xxxx as(
                      select 999 w, null c, 'aaa' name from dual union all
                      select null, 222, 'aaa' from dual union all
                      select 911,null, 'bbb' from dual union all
                      select 922, null, 'bbb' from dual union all
                      select null, 111, 'bbb' from dual union all
                      select 933, null ,'ccc' from dual union all
                      select null, 333, 'ddd' from dual union all
                      select null, 444, 'ddd' from dual)
                      select Row_Number() over(order by name,w,c) as rn,
                      w,c,name
                      from (select w,c,name,
                            count(w) over(partition by name) as cnt1,
                            count(c) over(partition by name) as cnt2
                            from xxxx)
                      where 1 <= all(cnt1,cnt2);
                      
                      RN     W     C  NAM
                      --  ----  ----  ---
                       1   999  null  aaa
                       2  null   222  aaa
                       3   911  null  bbb
                       4   922  null  bbb
                       5  null   111  bbb
                      myHomePage
                      http://www.geocities.jp/oraclesqlpuzzle/
                      • 8. Re: SQL Query
                        Mac_Freak_Rahul
                        off topic : Mate could you please tell me how could i view your geocities page in english . Looks like a very good page but too bad I dont understnad that language.

                        Regards
                        Rahul
                        • 9. Re: SQL Query
                          KanchDev
                          Hi Rahul,

                          Try this

                          http://translate.google.lk/translate?hl=en&sl=ja&tl=en&u=http%3A%2F%2Fwww.geocities.jp%2Foraclesqlpuzzle%2F

                          Cheers
                          Kanchana
                          • 10. Re: SQL Query
                            Mac_Freak_Rahul
                            Hey ,

                            Thanks Kanchana :)

                            Regards
                            Rahul