Select only rows with shared status from a table in SQL Server

Question

I have a table as follows:

ID CustomerID AccountNumber StatusID ------------------------------------- 1 300 300100 1 2 300 300200 3 3 300 300300 3 4 400 400100 1 5 400 400200 1 6 500 500100 1 

StatusID:

• 1 = Approved
• 3 = Pending

Now I need to select all the clients whose accounts are approved (none of them are expected), but not the clients whose accounts have not yet been sent.

Please let me know if you need more details from my end.

Answer 1

This is another easy way to do, based on your sample data.

 SELECT DISTINCT T.ID, T.CustomerID , T.AccountNumber , T.StatusID FROM Table1 T INNER JOIN (SELECT CID FROM @Table1 WHERE StatusID = 3) TT ON T.CID <> TT.CID 

Answer 2

Group by the customer and take only those who do not have status <> 1

 select customerID from your_table group by customerID having sum(case when status <> 1 then 1 else 0 end) = 0 

Answer 3

Use HAVING with MAX , this will give you all customers who have only the status "Approved" = 1

 SELECT CustomerID FROM @tblTest GROUP BY CustomerID HAVING MAX(StatusID)=1 

Answer 4

Listing all customers with pending bills is easy:

 select customerID from your_table where StatusID = 3 

And thus, just select the data in which the client is not listed above:

 select distinct CustomerID from your_table where CustomerID not in (select customerID from your_table where StatusID = 3) 

( distict to avoid duoplicates.)

Answer 5

The first answer seems to work. Another alternative way would be to make it a bit longer though

  select distinct customerid from ( select customerid,count(1) as cnt from (select customerid,statusID,count(1) as cnt from ids group by customerid,statusid)a group by customerid having count(1)=1 )a 

Answer 6

Just another way to do this with a simple self join.

Scheme:

 CREATE TABLE #TAB (ID INT, CustomerID INT, AccountNumber VARCHAR(20), StatusID INT) INSERT INTO #TAB SELECT 1,300,300100, 1 UNION ALL SELECT 2,300,300200, 3 UNION ALL SELECT 3,300,300300, 3 UNION ALL SELECT 4,400,400100, 1 UNION ALL SELECT 5,400,400200, 1 UNION ALL SELECT 6,500,500100, 1 

And do a Self Join, as shown below, and filter out the Where clause, which has pending status records.

 SELECT AP.* FROM #TAB AP LEFT JOIN #TAB P ON AP.CustomerID = p.CustomerID AND P.StatusID=3 WHERE AP.StatusID=1 AND P.ID IS NULL 

Answer 7

You can use a simple query as shown below to get all customers whose accounts are approved

 select distinct(customerID) from your_table where StatusID = 1