Find all records that share specific comment

user_330271 0
Viewed 52

I have an existing table1:

User   Comment  Group
---------------------
1      foo      a
2      bar      a
3      baz      b
4      123      a
5      bar      c
6      foo      d
7      654      a

Assume that I need to select all Users with Comment foo

select * from [table1] t1 where t1.[Comment] = 'foo';

User   Comment   Group
---------------------
1      foo       a
6      foo       d

Now I want to find all the users in the same Groups:

User   Comment  Group
---------------------
1      foo      a
2      bar      a
4      123      a
6      foo      d

How can I do this? I thought like this:

select t1.*
from   [table1] t1
left join [table1] t2 on t1.[User] = t2.[User]
and t1.[Comment] = 'foo'

But this is returning all records in [table1]. What am I doing wrong?

subquery tsql sql-server sql database
2 Answers

You could use exists:

select t.*
from mytable t
where exists (
    select 1 from mytable t1 where t1.comment = 'foo' and t1.group = t.group
)

For performance with this query, you want an index on (comment, group).

user_106767160

With the operator IN:

select * from tablename
where [Group] in (
  select [Group] from tablename
  where [Comment] = 'foo'
)

See the demo.
Results:

> User | Comment | Group
> ---: | :------ | :----
>    1 | foo     | a    
>    2 | bar     | a    
>    4 | 123     | a    
>    6 | foo     | d    
>    7 | 654     | a
user_104988280
Related