Idan Shechter Idan Shechter - 1 year ago 81
SQL Question

Group by query, each group has to not have any item not in a List

In need a query that will help me solve this.

Here's my table 'tags':

  • id (int)

  • name (String)

  • user_id (int)

  • hardware_id (int)

I am grouping the results of the 'tags' table by hardware_id. I also have a List of tags (

I want to get the hardware Id of the groups that all of the tags in the custom List matches at a
in the table above.

In other words, I want to get the hardware_id's that the custom List tags matches their
's. There might be
's that doesn't have a match in the custom list, but all of the custom list tags, must be in the group, and if it satisfies this need, I can the Id of that group.

I found it hard to explain and I didn't get an answer for that. I thought about doing it with foreach because it was so hard to solve, but I couldn't do it either and it's very inefficient to do it that way.


List : ['tag1', 'tag2']

Table Rows:

1, tag1, 5, 1
2, tag2, 5, 1
3, tag3, 5, 1
4, tag4, 5, 2
5, tag5, 6, 2

In this case, I should get the hardware_id of 1, because although one of the hardware Id's have tag3, it doesn't have any rows with a tag name that isn't in the List. IF the List had 'tag4', the hardware_id = 1 WOULDN'T be returned, because the List has a tag that the hardware_id group doesn't have.

If the Group doesn't have an item that the List has, it won't appear in the final result.

Someone game me this code, but it didn't work:

List<decimal> matchingHardareIds = db.tags.GroupBy(x => x.hardware_id)
.Where(x => x.All(s => tags.Contains( || 0 == tags.Count() && (s.user_id == userId)))
.Select(x => x.Key).ToList();

In that query, when I have one tag in the List and in the table I have several items with hardware_id 1 and one of them has a 'name' that is equal to the value in the List it will return empty results. this is because the rows in the table for a specific group by hardware_id, has a row with a name that doesn't appear in the custom List.

I need a query in either Entity Framework or Linq. Thanks a lot.

Answer Source

Use this:

var t = db.tags.GroupBy(x => x.hardware_Id)
          .Where(x => tags.All(y => 
                          x.Any(z=> == y)))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download