Querying specific relation that doesnt have a value on a column

I have the following tables:

• Person (id, name, address)

• Person_subscription (id, person_id, status)

Does a person have many subscriptions but no active one? get that person as ‘Ex-Customer’

Does a person have an active subscription? get that person as ‘Customer’

Ignore customers who don’t have any subscriptions.

SELECT person_id, 
CASE 
WHEN status = 'inactive' then 'Ex-Customer'
WHEN status = 'active' then 'Customer'
END AS account_status
FROM person_subscription
WHERE person_id = '1'
group by company_id;

The above query will bring out the selected person as an example.

person_id | account_status
   1      | Ex-Customer
   1      | Customer

But I’ll have two rows with conflicting values. My goal is to unify those rows into one and determine the condition set above.

SELECT person_id, status
FROM person_subscription
WHERE NOT EXISTS (
  SELECT * FROM company_subscription
  WHERE status='active'
)

The above query doesn’t yield any results.

What’s my best bet here, how to proceed?

Answer

You can use distinct on:

SELECT DISTINCT ON (person_id) person_id, 
       (CASE WHEN status = 'inactive' then 'Ex-Customer'
            WHEN status = 'active' then 'Customer'
        END) AS account_status
FROM person_subscription
WHERE person_id = 1
ORDER BY person_id, status ASC;