SQL query based on ALL or alternative or some other easier way

Question

/* Create a table called NAMES */
CREATE TABLE test(Id integer PRIMARY KEY, group text, content text);

/* Create few records in this table */
INSERT INTO test VALUES(1,'mygroup','foobar');
INSERT INTO test VALUES(2,'myothergroup','foobar');
INSERT INTO test VALUES(3,'myothergroup','foobaz');
INSERT INTO test VALUES(4,'gr1','foobaz');
INSERT INTO test VALUES(5,'gr0','foobaz');
COMMIT;

I have a SQL table like above.

I want to find all the content, that is present in all the group starting my.

My query looks like below :

SELECT DISTINCT content from test WHERE group like 'my%' and content = 
ALL(SELECT content from test WHERE group like 'my%');

which seems to be invalid as it returns nothing, it should return foobar because foobar is present in all the possible groups starting with my.

Eg: 2

Let's say I want to find all the content present in all the groups starting with gr:

SELECT DISTINCT content from test WHERE group like 'gr%' and content = 
ALL(SELECT content from test WHERE group like 'gr%');

Here, in this case it works totally fine and returns foobaz as foobaz is present in all the possible group starting with gr.

Please help.

Answer 1

You seem to want:

select content
from test
where group like 'my%'
group by content
having count(distinct group) = (select count(distinct group) from test where group like 'my%');