1
/* 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.

zubug55
  • 729
  • 7
  • 27

2 Answers2

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%');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

All you need is a join query

SELECT DISTINCT t1.content from test t1 join test t2 on t1.Id=t2.Id and t1.group_g like 'my%'

SELECT DISTINCT t1.content from test t1 join test t2 on t1.Id=t2.Id and t1.group_g like 'gr%'
Chirag
  • 57
  • 6