SQL Query for one to many bridge table-CodePudding

I am trying to find a logic to return the unique group from TableGroup where country of all members from bridge table TableMember is equal to US. if any of the member is from other country then it should not show in the result. in this example query should return only Group1

CodePudding user response：

This should be possible with

SELECT * FROM TableGroup
WHERE GroupId IN (
  SELECT GroupId
  FROM TableMember
  GROUP BY groupid
  HAVING COUNT(distinct country) = 1
)

Live example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b341d238cc6559575d7fb45eb625a421

CodePudding user response：

You just need a NOT EXISTS clause that checks for any members that don't match

SELECT g.*
FROM TableGroup g
WHERE NOT EXISTS (SELECT 1
    FROM TableMember m
    WHERE m.GroupId = g.GroupId
      AND m.Country <> 'US'
);

Or if you want to check that there also exist members from US

SELECT g.*
FROM TableGroup g
WHERE EXISTS (SELECT 1
    FROM TableMember m
    WHERE m.GroupId = g.GroupId
    GROUP BY ()
    HAVING COUNT(CASE WHEN m.Country <> 'US') = 0
);