CODEWITHSUNDEEP
sql-server
prameela rani
prameela rani

Reputation: 223

Select only rows with common status from table in SQL Server

I have 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:

Now I need to select all customers whose accounts are approved (none are pending), but not customers whose accounts are still pending.

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

Upvotes: 5

Views: 1612

Answers (7)

srinivasdgs
srinivasdgs

Reputation: 1

You can use simple query like below to get the all customers whose accounts are approved

select distinct(customerID) 
from your_table 
where StatusID = 1

Upvotes: 0

mohan111
mohan111

Reputation: 8865

This is the another simple way to do basing on your sample data provided

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

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Just an another way of doing is with simple self join.

Schema:

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 make a Self Join like below, and filter with Where clause who are having 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

Upvotes: 0

Aparna
Aparna

Reputation: 286

The first answer seems to work.Another alternative way would be to do this which is a bit lengthy 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

Upvotes: 0

Richard
Richard

Reputation: 108975

It is easy to get a list of all customers with pending accounts:

select customerID
from your_table
where StatusID = 3

And thus just select data where customer is not in the above:

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

(distict to avoid duoplicates.)

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use HAVING Clause with MAX, it will give you all customers who has only status "Approved" =1

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

Upvotes: 3

juergen d
juergen d

Reputation: 204746

Group by the customer and take only those having no status <> 1

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

Upvotes: 4

Related Questions