CODEWITHSUNDEEP
sqlsql-server
Harsh Gupta
Harsh Gupta

Reputation: 327

Filter Data from table based on different combinations

Today, we are working on SQL server query and facing one small issue in a simple logic. We have a table which is having 3 columns. You can see the structure of table as given below. 

ID | Name | FKId
1     a       1
2     b       1
3     c       1
4     a       2
5     b       2
6     a       3
7     c       3
8     b       5
9     c       5

In the above table, you can see a column 'Name' which has three different type of values a,b,c and 'FKId' is a foreign key column. In our result, we need those rows where we have a combination of 'Name' column values as a,b and a,c for each 'FKId'. No other combination is accepted. In the above table, we need below result. 

ID | Name | FKId
4     a       2
5     b       2
6     a       3
7     c       3

Upvotes: 0

Views: 274

Answers (1)

user1919238
user1919238

Reputation:

I would do something like this:

with fkids as (
   select fkid,
          max(case when name='a' then 1 else 0 end) as has_a,
          max(case when name='b' then 1 else 0 end) as has_b,
          max(case when name='c' then 1 else 0 end) as has_c
      from table
      group by fkid
)
select table.* from table
    join fkids on
        fkids.fkid = table.fkid and (
           (has_a = 1 and has_b = 1 and has_c = 0) or 
           (has_a = 1 and has_b = 0 and has_c = 1)
        )

Upvotes: 1

Related Questions