Return if all values meet the criteria

Question

I am trying to return all rows that have records in the child table where all of them meet the criteria

T1:
1
2
3
4
5

T2:

T.ID  Id  Value
1     01  Apple
1     02  Banana
2     05  Grapes
2     07  Kiwi
2     08  Apple
3     04  Banana
3     06  Potato
4     10  Honeydew
4     11  Berries
5     14  Apple
5     15  Kiwi
5     16  Radish

I need to identify all ids in T1 which are Fruit in T2, T2.value in (Apple,grapes,banana,kiwi,honeydew,berries)
Desired Output:

1
2
4

I have tried this:

select t1.id from t1,t2
where t1.id = t2.id
and t2.value  in ('Apple','Grapes','Banana','Kiwi','Honeydew','Berries') 

but it doesnt return the output I need, it also returns #3&5.

Answer 1

Adding one more possible answer here.

You can use EXISTS in such cases.

SELECT
    T1.ID
FROM
    T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            T2
        WHERE
            T2.T1_ID = T1.ID
            AND T2.VALUE NOT IN (
                'Apple',
                'Grapes',
                'Banana',
                'Kiwi',
                'Honeydew',
                'Berries'
            )
    )

db<>fiddle demo

Cheers!!

Answer 2

Assuming that you want ids that have at least one row in t2

select t2.id
from t2
group by t2.id
having sum(case when t2.value not in ('Apple', 'Grapes', 'Banana', 'Kiwi', 'Honeydew', 'Berries')
                then 1 else 0
           end) = 0;  -- nothing that isn't fruit

Answer 3

Apparently what you're trying to do is eliminate groups which contain items which are not fruits:

SELECT T1.ID
  FROM T1
  WHERE T1.ID NOT IN (SELECT T2.T1_ID
                        FROM T2
                        WHERE T2.VALUE NOT IN ('Apple','Grapes','Banana','Kiwi','Honeydew','Berries'))
  ORDER BY T1.ID

dbfiddle here