Forum Stats

  • 3,838,652 Users
  • 2,262,389 Discussions
  • 7,900,721 Comments

Discussions

How can I apply query for criteria?

583087
583087 Member Posts: 20
edited Aug 29, 2007 11:05AM in SQL & PL/SQL
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?

Comments

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond
    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?
  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond
    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
  • 583087
    583087 Member Posts: 20
    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
  • Warren Tolentino
    Warren Tolentino Member Posts: 5,543 Silver Trophy
    edited Aug 29, 2007 10:25AM
    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.
  • Himanshu Kandpal
    Himanshu Kandpal Member Posts: 1,971 Silver Badge
    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
  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond
    -- 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>
  • 590108
    590108 Member Posts: 97
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    550921
This discussion has been closed.