Multiple conditions for one column after group by-CodePudding

I am trying to group by Id then applying condition for the grouped results.

Group	LastName	FirstName
1	Smith	Jane
1	Smith	John
2	Doe	Beth
2	Doe	Jane
2	Doe	Jay
3	Ward	Bill
4	Adams	Beth
4	Grimes	Jane
4	Gag	Smelly

Here in the first Name column after group by if it contains both Jane and Beth under first name column then I have to select that Id

Expected results

Group	LastName	FirstName
2	Doe	Beth
2	Doe	Jane
2	Doe	Jay
4	Adams	Beth
4	Grimes	Jane
4	Gag	Smelly

CodePudding user response：

You would typically need in this case a group by where the first name is found in a per group and the count = 2. THEN join back to the original table for all people within those given groups.

select
      YT.Group
   from 
      YourTable YT
   where
      YT.FirstName in ('Jane', 'Beth') 
   group by
      YT.Group
   having
      count( distinct YT.FirstName ) = 2

The count of DISTINCT would prevent a false result if you had a Jane Doe and a Jane Smith in the same group. This would be a distinct count of 1 since there is no Beth in this example. So, now from this result, you can get the rest by making the above a prequery

select
      YT2.*
   from
      ( select
              YT.Group
           from 
              YourTable YT
           where
              YT.FirstName in ('Jane', 'Beth') 
           group by
              YT.Group
           having
              count( distinct YT.FirstName ) = 2 ) PQ
         JOIN YourTable YT2
            on PQ.Group = YT2.Group

CodePudding user response：

If you want your FirstName is fixed only for Jane and Beth then

SELECT GroupId, LastName, FirstName from
   test_table WHERE GroupId in (
      SELECT GroupId from test_table WHERE FirstName in ('Jane', 'Beth') 
)

NOTE : I have updated Group with GroupId in order to remove conflict with default SQL keywords.

Fiddle Here