I have two tables, clients and subscriptions. We can find many subscriptions in the table of subscriptions from a client. It is allied by a foreign key(client_id).

In the table subscriptions I have a parameter status, the values could be "active, start pending, cancelled..."

I want to get all the clients(SELECT * FROM clients), but I want to add new column in the query(STATUS_CLIENT): this column must be true if I find an status='active' in one of the subscriptions of the client and false if I dont get any active status in the table subscriptions.

Thanks!

CodePudding user response：

Please specify which DBMS you are using. Each DBMS has its own feature set and its own SQL dialect. For example, MS SQL Server does not have an actual boolean data type, while PostgreSQL does.

The actual solution could be to add an additional column, which contains a correlated subquery for table subscriptions. Something like this:

SELECT
  C.*,
  EXISTS (SELECT *
          FROM subscriptions
          WHERE client_id = C.client_id AND status = 'active') AS status_client
FROM
  clients C

Depending on the DBMS you use, you might need to tweak this conceptual query. If you need additional help, please first specify the actual DBMS or SQL dialect in your question.

CodePudding user response：

    select 
    *,
    case when subs.status is not null then true else false end as client_status
    from clients
    left join subscriptions as subs
    on clients.client_id = subs.client_id
and subs.status = 'active'

Steps:

1. Join on the clients table, which has distinct client id's
2. Join the rows that has status = active in the subscription table
3. Performing the Left Join with the second condition above would produce Nulls where the status is not active
4. Those nulls are equivalent to your False Logic while the active would be set to True

CodePudding user response：

SELECT *, CASE WHEN S.STATUS = 'ACTIVE' THEN TRUE
ELSE FALSE END AS STATUS_CLIENT
FROM CLIENTS AS C
LEFT JOIN SUBSCRIPTIONS AS S
ON C.CLIENT_ID = S.CLIENT_ID;