Oracle 21c

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

CREATE TABLE SAMPLE (DATE_OF_USE, VENUE, ROOM_LOG, LOG_NAME, PERSON_LOG) AS
         SELECT DATE '2023-09-01', 'ASU', 'Red Room', 'Log 12345', 'Jane, Doe' FROM DUAL UNION ALL
         SELECT DATE '2023-09-01', 'ASU', 'ROOM 01', 'Log 12345', 'Jane, Doe' FROM DUAL UNION ALL
         SELECT DATE '2023-09-02', 'MOR', 'Blue Room', 'Log 67890', 'John, Smith' FROM DUAL UNION ALL
         SELECT DATE '2023-09-02', 'MOR', 'ROOM 02', 'Log 67890', 'John, Smith' FROM DUAL UNION ALL
         SELECT DATE '2023-09-03', 'ASU', 'ROOM 03', 'Log 11222', 'Luis, Jacob' FROM DUAL UNION ALL
         SELECT DATE '2023-09-04', 'MOR', 'ROOM 04', 'Log 22211', 'Patel, Hannah' FROM DUAL UNION ALL
         SELECT DATE '2023-09-05', 'ASU', 'Red Room', 'Log 33333', 'Sparks, Noah' FROM DUAL UNION ALL
         SELECT DATE '2023-09-05', 'ASU', 'ROOM 01', 'Log 33333', 'Sparks, Noah' FROM DUAL
8 rows affected

SELECT 
   * 
FROM 
   SAMPLE
WHERE 
   LOG_NAME IN (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')


DATE_OF_USE
VENUE
ROOM_LOG
LOG_NAME
PERSON_LOG


01-SEP-23
ASU
Red Room
Log 12345
Jane, Doe

01-SEP-23
ASU
ROOM 01
Log 12345
Jane, Doe

05-SEP-23
ASU
Red Room
Log 33333
Sparks, Noah

05-SEP-23
ASU
ROOM 01
Log 33333
Sparks, Noah


SELECT 
    * 
 FROM 
    SAMPLE s1
 WHERE 
     EXISTS (
         SELECT NULL -- can be anything as value isn't referred to elsewhere
         FROM SAMPLE s2
         WHERE ROOM_LOG = 'Red Room'
         AND s2.LOG_NAME = s1.LOG_NAME -- correlation with main query
     )


DATE_OF_USE
VENUE
ROOM_LOG
LOG_NAME
PERSON_LOG


01-SEP-23
ASU
Red Room
Log 12345
Jane, Doe

01-SEP-23
ASU
ROOM 01
Log 12345
Jane, Doe

05-SEP-23
ASU
Red Room
Log 33333
Sparks, Noah

05-SEP-23
ASU
ROOM 01
Log 33333
Sparks, Noah

DATE_OF_USE	VENUE	ROOM_LOG	LOG_NAME	PERSON_LOG
01-SEP-23	ASU	Red Room	Log 12345	Jane, Doe
01-SEP-23	ASU	ROOM 01	Log 12345	Jane, Doe
05-SEP-23	ASU	Red Room	Log 33333	Sparks, Noah
05-SEP-23	ASU	ROOM 01	Log 33333	Sparks, Noah