CODEWITHSUNDEEP
sqloracle-database
MK12
MK12

Reputation: 491

Only when all statuses the same than return result

I am selecting Q_GRADEBOOK_ID if all the status against Q_GRADEBOOK_ID is 488 than return Q_GRADEBOOK_ID otherwise return empty

TABLE Q_GB_CAT_SCORE
---------------------------------------------------------
 Q_GRADEBOOK_ID | PERSON_ID | STATUS | Q_GRADEBOOK_CAT_ID
      1         |     1     |  488   |       1
      1         |     1     |  488   |       2
      1         |     2     |  488   |       1
      2         |     2     |  486   |       1
      2         |     2     |  488   |       2

My Query

 SELECT
 DISTINCT 
 Q_GRADEBOOK_ID 
 FROM 
 Q_GB_CAT_SCORE WHERE
 STATUS IN ('488')

This query return this result

Q_GRADEBOOK_ID
      1        
      2

Expected Result

Q_GRADEBOOK_ID
      1

I expect it because Q_GRADEBOOK_ID 1 return because against Q_GRADEBOOK_ID 1 all the status is 488.

Upvotes: 0

Views: 36

Answers (2)

Ori Marko
Ori Marko

Reputation: 58832

You can exclude ID with different status:

SELECT distinct 
 Q_GRADEBOOK_ID
  FROM Q_GB_CAT_SCORE
 WHERE Q_GRADEBOOK_ID not in
        (select distinct Q_GRADEBOOK_ID FROM Q_GB_CAT_SCORE WHERE status <> '488')

Upvotes: 2

forpas
forpas

Reputation: 164174

You can GROUP BY Q_GRADEBOOK_ID and put the condition in the HAVING clause:

SELECT Q_GRADEBOOK_ID 
FROM Q_GB_CAT_SCORE 
GROUP BY Q_GRADEBOOK_ID
HAVING COUNT(*) = SUM(CASE WHEN STATUS = '488' THEN 1 ELSE 0 END)

or

SELECT Q_GRADEBOOK_ID 
FROM Q_GB_CAT_SCORE 
GROUP BY Q_GRADEBOOK_ID
HAVING SUM(CASE WHEN STATUS <> '488' THEN 1 ELSE 0 END) = 0

Upvotes: 1

Related Questions