CODEWITHSUNDEEP
sqlsql-server
s p
s p

Reputation: 799

Get a particular record based on a condition in SQL

My requirement is to get id for missing status from SQL table. I will get a list of status for each id, say A,B,C,D. In a scenario, I have to check status B exists or not. Table gets updated everyday and each time new Id will be created

Conditions,

  1. If status A exists and other statuses such as C and D does not exists, then don't need to get id.
  2. If status A and B exists and other statuses such as C or D does not exists, then don't need to get id .
  3. If status A exists and B not exists, other statuses such as C or D exists, then I should get the id of that record
  4. If status A and B exists, other statuses such as C or D exists (all status exists), then I don't need to get the id of that record
    Table1:  
    Id StatusCode  
    1    A   
    1    C    
    2    A    
    2    B     
    2    C    
    3    A    
    3    C     
    3    D

How do I get Id 1 and 3 using SQL query?, Seems simple but as I am new to SQL I could not able to get it in SQL.

enter image description here

select statement in this screenshot works fine when there is only one id, it fails on multiple id. I tried many other way, but no use

Upvotes: 0

Views: 1299

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This answers the original version of the question.

I think you can use aggregation:

select id
from t
group by id
having sum(case when status = 'A' then 1 else 0 end) > 0 and
       sum(case when status in ('C', 'D') then 1 else 0 end) > 0;

Upvotes: 1

Never Die
Never Die

Reputation: 331

Try this

SELECT DISTINCT ID 
FROM T1
WHERE Statuscode = 'A' AND ID NOT IN (SELECT ID FROM T1 WHERE Statuscode = 'B' )
AND  (ID IN (SELECT ID FROM T1 WHERE Statuscode = 'C' ) OR ID IN (SELECT ID FROM T1 WHERE Statuscode = 'D' ))

FIDDLE DEMO

Also, To correct Gordon Linoff's answer, we need to add one more where criteria there

SELECT Id
FROM T1
GROUP BY Id
HAVING SUM(CASE WHEN Statuscode = 'A' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN Statuscode = 'B' THEN 1 ELSE 0 END) = 0 AND
       SUM(CASE WHEN Statuscode IN ('C', 'D') THEN 1 ELSE 0 END) > 0;

FIDDLE DEMO

Upvotes: 2

Iria
Iria

Reputation: 497

select distinct id from t where t.statuscode = 'C' or t.statuscode = 'D' group by t.id

Upvotes: 0

A_kat
A_kat

Reputation: 1527

SELECT id
FROM t
GROUP BY
    Id
HAVING MAX(status) = CHAR(64 + COUNT(*))
--char(64+1) = A, char(64+2) = B etc

The logic behind this is that it will take all count the same types of id. So if you have 3 rows you will need abc. If you have an id with 4 rows you will have ABCD. Generally the max status should always be the same as the number of rows.

This is true of course if you have no duplicate between id and status code.

Upvotes: 0

Related Questions