Sql select based on the value in other rows

Question

I have a table like below:

DS_no   Value   Language   Nach
1        0        EN        123
2        ABC      EN        123
2        DCEF     EN        123
1        1        EN        122
2        XZU      EN        122
2        SDF      EN        122

So i wanted my select statement to check if the value of DS_no which has 1 for every nach. If the value is 1 then it should select all other values for that nach. eg. the ds_no 1 of nach 122. If the ds_no 1 has value 0 then the select should not return any values.

eg: the ds_no 1 of nach 123 has the value 0. so i dont want to select any values of nach 123. but the ds_no 1 of nach 122 has the value 1. so i want to select all the values of nach 122 except the value 1.

Answer 1

Sample data as you posted

SQL> select * from test;

     DS_NO VALU       NACH
---------- ---- ----------
         1 0           123
         2 abc         123
         2 dcef        123
         1 1           122
         2 xzu         122
         2 sdf         122

6 rows selected.

Query that returns what you described, with a side note that you - most probably - made a typo, here:

so i want to select all the values of nach 123 except the value 1.

123 should be 122, I presume.

SQL> select ds_no, value, nach
  2  from test a
  3  where value <> '1'
  4    and exists (select null
  5                from test b
  6                where b.nach = a.nach
  7                  and b.value = '1'
  8               );

     DS_NO VALU       NACH
---------- ---- ----------
         2 sdf         122
         2 xzu         122

SQL>