Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;


3 rows affected

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;
4 rows affected

Select  Distinct
           c.owner_id, c.pet_id, c.name, c.address, 
           DECODE(Nvl(v.STATUS, 'INACTIVE'), 'INACTIVE', 'INACTIVE', 'ACTIVE')  as status 
From       CUSTOMERS_TABLE c
Left Join  VISITS_TABLE v ON(v.owner_id = c.owner_id And v.pet_id = c.pet_id)
Order By  c.owner_id, c.pet_id


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

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