TSQL Query Where All Records Must Exists to Return A Record

I am not sure even how to ask this question.

I have a table of tags:

TagId  Tag
-----  -----
1      Fruit
2      Meat
3      Grain

I have a table of events:

EventId   Event
-------   -----------
1         Eating开发者_如何学编程 Food
2         Buying Food

What I need to do is bring back only Events that have all selected tags associated with it.

If three tags are selected then only show event that have all three.

For example:

Mapping Table

EventId   TagId
-------   -----
1         1
1         3
2         1

If I write a query like this:

select * from MapTable where where tagId in (1,3)

This will return Eating Food and Buying Food.

But what I need to do is bring back events that have both tags 1 and 3. This means that the only event in this case I would return would be Eating Food as it has both selected tags.

I was wondering if this can be done in TSQL or if I will have to use the business layer to translate it into the object to return back to the GUI.

Thanks.

There was a very similar question yesterday: Query for exact match of users in a conversation in SQL Server

basically you can do this:

DECLARE @NumTags INT = 2

SELECT EventID
FROM EventTag
GROUP BY EventID
HAVING
   Sum(CASE WHEN TagID IN (1, 3) THEN 1 ELSE 0 END) >= @NumTags

so this will find all events that both the tags exist in (this allows for instances where those two tags exist along with any additional tags)

Here is a solution for when you do not know the tags before hand.

Load the tags into a table variable and get the total count:

select @iCount = COUNT(*) from @Tags;

Then write your normal query and slam those results into a table variable:

insert into @EventTags(IsSet, EventId)
    select distinct CASE WHEN TagID IN (select ID from @Tags) THEN 1 ELSE 0 END, 
           e.EventId
        from  Event_Tag e
            inner join @Tags t on t.ID = e.TagId

Then to get back only Events that have ALL matching tags, not just ones that are in the selection, but ALL you do this:

select * 
    from Event_Tag e
    inner join @Tags t on t.ID = e.TagId
        where e.EventId in 
              (  select EventId 
                    from @EventTags
                        group by EventId
                            having count(EventId) = @iCount
              )

Only bring back tags that have all tags associated.

Thank you again everyone for the ideas! Greatly appreciated all the feedback!

There's probably a better way to write it but this will give you what you are looking for:

select *
from event e
where exists(select * from maptable where eventid = e.eventid and tagid = 1) and exists(select * from maptable where eventid = e.eventid and tagid = 3)

You'll want to inner join the two tables, as follows

SELECT * FROM Events INNER JOIN MapTable ON MapTable.EventId=Events.EventID WHERE MapTable.TagID=1 AND MapTable.TagID=3