8 Replies Latest reply: Aug 29, 2007 10:05 AM by Aketi Jyuuzou RSS

    How can I apply query for criteria?

    583087
      Please consider this table given below:-
      CODE PLACE
      A P1
      A P2
      B P1
      C P2
      D P1
      D P2

      Now I just want to retreive code which is only associating with place P1. I expect the result here to be B only.
      How can I apply query here?
        • 1. Re: How can I apply query for criteria?
          BluShadow
          But A and D are also associated with place P1 so why only B? What is the criteria/logic that determines it should only be B?
          • 2. Re: How can I apply query for criteria?
            BluShadow
            Perhaps you mean like this:
            SQL> with t as (select 'A' as code, 'P1' as place from dual union all
              2             select 'A', 'P2' from dual union all
              3             select 'B', 'P1' from dual union all
              4             select 'C', 'P2' from dual union all
              5             select 'D', 'P1' from dual union all
              6             select 'D', 'P2' from dual)
              7  -- end of test data
              8  select code
              9  from t
             10  where place = 'P1'
             11  and 0 = (select count(*) from t t2 where t2.code = t.code and t2.place <> t.place)
             12  /
            
            C
            -
            B
            • 3. Re: How can I apply query for criteria?
              583087
              Thanks for the reply. Yes. I am in the situation to filter out the CODE which is only associated with place P1. If it is also associated with P2, then I should not consder this CODE. I hope you got what I said.

              TIA
              • 4. Re: How can I apply query for criteria?
                Warren Tolentino
                not sure if i understand correctly your posting. maybe a simple where conditions in your query something like below will do:
                SQL> with virtual_table as
                  2   (select 'A' code, 'P1' place from dual union
                  3    select 'A' code, 'P2' place from dual union
                  4    select 'B' code, 'P1' place from dual union
                  5    select 'C' code, 'P2' place from dual union
                  6    select 'D' code, 'P1' place from dual union
                  7    select 'D' code, 'P2' place from dual)
                  8  select * from virtual_table
                  9   where place = 'P1'
                10     and code  = 'B';

                C PL
                - --
                B P1

                SQL>
                maybe if you can post some sample output we might exactly understand your posting.
                • 5. Re: How can I apply query for criteria?
                  Himanshu Kandpal
                  Hi,

                  try this , what will happen if B have more than one records having value = P1.

                  -- un tested
                  SELECT CODE, PLACE FROM table1 WHERE PLACE = P1 AND CODE IN
                  ( SELECT CODE FROM TABLE1 HAVING COUNT(*) = 1
                  GROUP BY CODE )


                  Thanks
                  • 6. Re: How can I apply query for criteria?
                    BluShadow
                    -- un tested
                    SELECT CODE, PLACE FROM table1 WHERE PLACE = P1 AND
                    CODE IN
                    ( SELECT CODE FROM TABLE1 HAVING COUNT(*) = 1
                    GROUP BY CODE )
                    That doesn't work if B is associated to P1 more than once...
                    SQL> with t as (select 'A' as code, 'P1' as place from dual union all
                      2             select 'A', 'P2' from dual union all
                      3             select 'B', 'P1' from dual union all
                      4             select 'B', 'P1' from dual union all
                      5             select 'C', 'P2' from dual union all
                      6             select 'D', 'P1' from dual union all
                      7             select 'D', 'P2' from dual)
                      8  -- end of test data
                      9  SELECT CODE, PLACE
                     10  FROM t WHERE PLACE = 'P1'
                     11  AND CODE IN (SELECT CODE FROM t HAVING COUNT(*) = 1 GROUP BY CODE)
                     12  /
                    
                    no rows selected
                    
                    SQL>
                    • 7. Re: How can I apply query for criteria?
                      590108
                      Check this

                      <p><font face="Courier New" size="2">sqlplus> ed<br>
                      Wrote file afiedt.buf<br>
                      <br>
                      1 with t as (select 'A' as code, 'P1' as place from dual union all<br>
                      2 select 'A', 'P2' from dual union all<br>
                      3 select 'B', 'P1' from dual union all<br>
                      4 select 'C', 'P2' from dual union all<br>
                      5 select 'D', 'P1' from dual union all<br>
                      6 select 'D', 'P2' from dual union all<br>
                      7 select 'E', 'P1' from dual union all<br>
                      8 select 'G', 'P1' from dual union all<br>
                      9 select 'F', 'P2' from dual union all<br>
                      10 select 'H', 'P1' from dual union all<br>
                      11 select 'L', 'P1' from dual)<br>
                      12 select code,place<br>
                      13 from t<br>
                      14 where<br>
                      15 --place = 'P1' and<br>
                      16* not exists ( select t2.place from t t2 where t2.code = t.code and t2.place
                      <> t.place )<br>
                      sqlplus> /<br>
                      <br>
                      C PL<br>
                      - --<br>
                      B P1<br>
                      C P2<br>
                      E P1<br>
                      G P1<br>
                      F P2<br>
                      H P1<br>
                      L P1<br>
                      <br>
                      7 rows selected.<br>
                      <br>
                      Elapsed: 00:00:00.00</font></p>

                      add your PLACE filter

                      Good Luck
                      • 8. Re: How can I apply query for criteria?
                        Aketi Jyuuzou
                        SQL> with t as (select 'A' as code, 'P1' as place from dual union all
                          2             select 'A', 'P2' from dual union all
                          3             select 'B', 'P1' from dual union all
                          4             select 'B', 'P1' from dual union all
                          5             select 'C', 'P2' from dual union all
                          6             select 'D', 'P1' from dual union all
                          7             select 'D', 'P2' from dual)
                          8  SELECT CODE, max(PLACE)
                          9    FROM t
                        10   group by CODE
                        11  having min(case when place = 'P1' then 1 else 0 end) = 1;
                        [pre]
                        C MA
                        - --
                        B P1


                        alike thread
                        simple select?