Using Sql server with In

I have two tables Tbl_Event (primary key:-EventID) and tbl_items.

tbl_items table contains multiple items for same EventID with status 0 or 1.

Sample Data:

Tbl_event:
EventId  Name
5        Test
6        Seminar
7        Meet

tbl_items
ItemId    EventId  status 
1          5         0
2          6         1
2          6         0
3          7         1
3          7         1

I just need to get Event "Meet" having both status value 1.

This is what I tried:

SELECT     *
FROM       Tbl_items L
INNER JOIN Tbl_Events E ON L.EventId = E.EventId 
WHERE      L.Eventid NOT IN (SELECT Eventid FROM TBL_VMS_LENT_ITEMS WHERE Status = 0)

Solution

One approach is to use a subquery which aggregates over events and checks that all statuses are 1:

WITH cte AS (
    SELECT EventId
    FROM tbl_items
    GROUP BY EventId
    HAVING SUM(CASE WHEN status <> 1 THEN 1 ELSE 0 END) = 0
)

SELECT t1.*
FROM Tbl_event t1
INNER JOIN cte t2
    ON t1.EventId = t2.EventId;

If you just want the event IDs, use SELECT * FROM cte, otherwise use the full query I gave immediately above. This approach will generalize to more complex logic, should you need it later.