sqloracle-database

How to format nested select statement or join which pulls back mutliple-row conditional into one result?


I'm trying to aggregate multiple results from another table into one result in a nested select statement. I have a one-to-many relationship with single rows from the first/main table correlating to multiple rows from the second table.

I currently have a main grid set up for the 'Customers', with a sub-grid for each 'Customer' row, expandable to show their 'Visits'. I want to have a 'Status' for the 'Customer' grid so that the users don't have to expand the 'Visits' sub-grid to check the overall status of a 'Customer'. If any 'Visits' are active, it should result in an 'Active' Customer status.

An example the way the tables may currently be set up

CUSTOMERS_TABLE
owner_id
pet_id
name
address
otherinfo...

VISITS_TABLE
owner_id
pet_id
status
otherinfo...

I want something like this, where I can check if ANY of the correlated visits in the VISITS_TABLE are active/inactive, returning that as a status for the customer.

SELECT
    c.owner_id,
    c.pet_id,
    c.name,
    c.address,
    CASE WHEN v.status IN ('ACTIVE', 'PENDING') THEN 'ACTIVE' ELSE 'INACTIVE' END status
FROM
    CUSTOMERS_TABLE c
LEFT JOIN 
    VISITS_TABLE v ON
        c.owner_id = v.owner_id
        AND c.pet_id = v.pet_id

I have tried something similar, but when joining on the two IDs, I get a bunch of "duplicate" results, because it grabs another row for every row in the VISITS_TABLE. I just want one unified status, if any of the VISITS_TABLE results have 'Active' or 'Pending', it counts for them all. There isn't a more specific way of curating the Join, because I need to check all of the 'Visits' for each 'Customer'.

CURRENT (wrong) EXAMPLE

owner_id | pet_id | name | address | status
1             1      Jo      123     'Paid'
2             2      Blo     456     'Paid'
2             2      Blo     456     'Pending'
3             3      Shmo    789     'Inactive'
...

CORRECT (desired) EXAMPLE

owner_id | pet_id | name | address | status
1             1      Jo      123     'Paid'
2             2      Blo     456     'Active'
3             3      Shmo    789     'Inactive'
...

I feel like this should be easy and I'm just missing something obvious... any help?


Solution

  • Rather than JOINing, you can use EXISTS:

    SELECT owner_id,
           pet_id,
           name,
           address,
           CASE
           WHEN EXISTS(
                  SELECT 1
                  FROM   VISITS_TABLE v
                  WHERE  v.status   IN ('ACTIVE', 'PENDING')
                  AND    c.owner_id = v.owner_id
                  AND    c.pet_id   = v.pet_id
                )
           THEN 'ACTIVE'
           ELSE 'INACTIVE'
           END AS status
    FROM   CUSTOMERS_TABLE c
    

    Which, for the sample data:

    CREATE TABLE CUSTOMERS_TABLE (owner_id, pet_id, name, address) AS
      SELECT 1, 1, 'Alice', '1 The Street' FROM DUAL UNION ALL
      SELECT 2, 2, 'Beryl', '2 The Road'   FROM DUAL UNION ALL
      SELECT 3, 3, 'Carol', '3 The Avenue' FROM DUAL;
    
    CREATE TABLE VISITS_TABLE (owner_id, pet_id, status) AS
      SELECT 1, 1, 'PAID'    FROM DUAL UNION ALL
      SELECT 1, 1, 'ACTIVE'  FROM DUAL UNION ALL
      SELECT 2, 2, 'PAID'    FROM DUAL UNION ALL
      SELECT 2, 2, 'PENDING' FROM DUAL;
    

    Outputs:

    OWNER_ID PET_ID NAME ADDRESS STATUS
    1 1 Alice 1 The Street ACTIVE
    2 2 Beryl 2 The Road ACTIVE
    3 3 Carol 3 The Avenue INACTIVE

    fiddle