Bigquery – How to filter records with certain condition

I’m trying to write SQL (in BigQuery) the following in order to get a result that satisfies the following conditions. for ex: my table contains the following data

id         | value
___________|_____________
1          | p
1          | oo
2          | p
4          | p
4          | lop
5          | AA
5          | p
6          | p
6          | p

I want to filter out records where it contains only value as “p” from the table. The expected result would be

2          | p
6          | p

I have tried with the following query but it returns other rows as well (1,p and 1,oo)

SELECT id,value
FROM `ggg.tt.table` where userid in 
(
   select id from (SELECT id,COUNT(distinct value )as cnt
   from (select * FROM `ggg.tt.table` where trim(value) = 'p' 
     )group by 1 having cnt = 1))

can someone help how to achieve this using bigquery ?

Answer

You can count the distinct values for every id

CREATE TABLE tab1
    (`id` int, `value` varchar(3))
;
    
INSERT INTO tab1
    (`id`, `value`)
VALUES
    (1, 'p'),
    (1, 'oo'),
    (2, 'p'),
    (4, 'p'),
    (4, 'lop'),
    (5, 'AA'),
    (5, 'p'),
    (6, 'p'),
    (6, 'p')
;

SELECT DISTINCT id 
FROm  tab1 t1 
WHERE `value` = 'p' AND (SELECT COUNT(DISTINCT `value`) FROM tab1 WHERE id = t1.id)  = 1

| id |
| -: |
|  2 |
|  6 |

db<>fiddle here