how to select set of records is ID is present in one of them

Here is the table where ORGID/USERID makes unique combination:

ORGID   USERID
1       1
1       2
1       3
1       4
2       1
2       5
2       6
2       7
3       9
3       10
3       11

I need to select all records (organizations and users) wherever USERID 1 is present. So USERID=1 is present in ORGID 1 and 2 so then select all users for these organizations including user 1 itself, i.e.

ORGID   USERID
1       1
1       2
1       3
1       4
2       1
2       5
2       6
2       7

Is it possible to do it with one SQL query rather than SELECT *.. WHERE USERID IN (SELECT…

Answer

Yes, you can do it with a single select statement – no in or exists conditions, no analytic or aggregate functions in a subquery, etc. Why you want to do it that way is not clear; in any case, it is possible that the solution below is also more efficient than the alternatives. You will have to test on your real-life data to see if that is true.

The solution below has two potential disadvantages: it only works in Oracle (it uses a proprietary extension of SQL, the match_recognize clause); and it only works in Oracle 12.1 or higher.

with
  my_table(orgid, userid) as (
    select 1,  1 from dual union all
    select 1,  2 from dual union all
    select 1,  3 from dual union all
    select 1,  4 from dual union all
    select 2,  1 from dual union all
    select 2,  5 from dual union all
    select 2,  6 from dual union all
    select 2,  7 from dual union all
    select 3,  9 from dual union all
    select 3, 10 from dual union all
    select 3, 11 from dual
  )
-- End of SIMULATED data (for testing), not part of the solution.
-- In real life you don't need the WITH clause; reference your actual table.
select *
from   my_table
match_recognize(
  partition by orgid
  all rows per match
  pattern (x* a x*)
  define  a as userid = 1
);

Output:

     ORGID     USERID
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          5
         2          7
         2          6