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?
Rather than JOIN
ing, 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 |