Search code examples
sql-servermultiple-records

SQL Grabbing all records for X Column that equal A, if any of those records include B in the Y column

I am trying to pull a report that will grab all activity logs for a certain object.

Essentially I am trying to pull from this:

Object, Activity, User, Timestamp, Group
Object1, Activity1, User, Timestamp, Home
Object1, Activity2, User, Timestamp, Away
Object1, Activity3, User, Timestamp, Away
Object2, Activity1, User, Timestamp, Away
Object2, Activity2, User, Timestamp, Away
Object3, Activity1, User, Timestamp, Home
Object4, Activity1, User, Timestamp, Away    
Object4, Activity1, User, Timestamp, Home

And I want to pull all Object records if any of the group column has a "Home" entry. I even want the records that do not have a "Home" in the column, as long as one of records in the object records include it at least once.

So for example:

Object1, Activity1, User, Timestamp, Home
Object1, Activity2, User, Timestamp, Away
Object1, Activity3, User, Timestamp, Away
Object3, Activity1, User, Timestamp, Home
Object4, Activity2, User, Timestamp, Away
Object4, Activity3, User, Timestamp, Home

Is there any way to accomplish this using MS SQL?

Solution

  • Use a subquery/cte to find objects with 'home', then join the full table to that:

    SELECT a.*
FROM YourTable a
JOIN (SELECT DISTINCT [Object]
      FROM YourTable
      WHERE [group] = 'Home'
      )b
ON a.[Object] = b.[Object]

    Demo: SQL Fiddle