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?

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?

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

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

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.

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

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>

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

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
550921