Search code examples
sqlgroup-byhaving

Select Rows with a specific id and all another fields in table is 1

I want to select a value from a table where all another value in same table is 1.

Lets say I have a field called GLOBAL_ID and in the table there is a multi same value for this GLOBAL_ID and there is another filed called IS_DELETED but with different value (1,0)

My Question is I want to select all GLOBAL_ID from Table Where (ALL) IS_DELETED have the same value (1)

Solution

  • Use aggregation:

    SELECT GLOBAL_ID
FROM tablename
GROUP BY GLOBAL_ID
HAVING MIN(IS_DELETED) = 1;

    The condition in the HAVING clause filters out all GLOBAL_IDs with at least one IS_DELETED = 0.