Filter data between two tables having one to many relationship

Client table:

Status table:

The client table is connected to status table on [CID]. I want the [CID] of the clients who doesn't have any status set to "Not Active".

Here's my attempt:

SELECT 
    A.[CID], B.[Status] 
FROM
    [Client table] AS A
INNER JOIN 
    [Status table] B ON A.[CID] = B.[CID]
WHERE 
    B.[Status] = 'Not Active' 

In this case it should show client with CID 2 & 3 and not show client with CID 1. However, I'm getting all CID 1, 2 and 3 when in putting the condition that Status = Not Active. With the above query I'm getting all the three CID's.

How can I improve my query to show only CID 2 & 3 and what am I doing wrong here?

Your inner join will returns ALL ROWS matching your criteria so you will get all CID with status that is at least once not active.
You should force the join and check for nulls.

SELECT A.[CID], 
       B.[Status] 
FROM [Client table] as A
JOIN [Status table] B ON A.[CID] = B.[CID]
LEFT JOIN [Status table] C ON C.[CID] = B.[CID] AND C.[Status] = 'Not Active' 
WHERE C.[CID] IS NULL

You could also do a GROUP BY or a COUNT(*)

SELECT A.[CID], 
       B.[Status] 
FROM [Client table] as A 
JOIN [Status table] B ON A.[CID] = B.[CID] 
WHERE (SELECT COUNT(*) 
       FROM [Status table] C WHERE A.[CID] = C.[CID] 
                            AND C.[Status] = 'Not Active') = 0

NOT EXISTS is the most efficient way to do this.

Unfortunately, SQL Server requires at least one colum in the subquery, so you can just do SELECT 1 or SELECT NULL. Whatever you do gets ignored, it's just looking for the existence (or not) of a row in the subquery. Note the correlation to the outer query in the inner WHERE.

SELECT
  c.CID
FROM Client c
WHERE NOT EXISTS (SELECT 1
    FROM Status s
    WHERE s.CID = c.CID   -- correlation
      AND s.Status = 'Not Active'
);

If you actually want to see all Status rows using a join then a window function might be the best option.

Here we use a window function which can calculate over multiple rows at once, while still returning all of them rather than aggregating. The PARTITION BY works like GROUP BY here, and the CASE WHEN makes it conditional so it only counts rows which match the condition.

SELECT
  c.CID
FROM Client c
JOIN (
    SELECT s.*,
      countNotActive = COUNT(CASE WHEN s.Status = 'Not Active' THEN 1 END) OVER (PARTITION BY s.CID)
    FROM Status s
) s ON s.CID = c.CID
WHERE s.countNotActive = 0;

> I want the [CID] of the clients who doesn't have any status set to "Not Active".

For me the clearest way to do this is to use EXCEPT, the SQL operator for (one-sided) set difference:

SELECT CID FROM Client
EXCEPT
SELECT CID FROM Status WHERE Status = 'Not Active'

This means "give me all the CIDs that are in the first SELECT but not in the second SELECT"

Now, if you need more than just the IDs of the relevant Clients, you'd probably want the NOT EXISTS approach. But if you just want the IDs, this will do.