Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users
    ("NAME" varchar2(5), "USER_ID" int, "ADDRESS" varchar2(9))
;

INSERT ALL 
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('Jaya', 6, 'Chennai')
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('Krena', 5, 'Mumbai')
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('Johan', 4, 'Delhi')
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('Ivan', 3, 'Chennai')
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('Tom', 2, 'Hyderabad')
    INTO users ("NAME", "USER_ID", "ADDRESS")
         VALUES ('John', 1, 'Bangalore')
SELECT * FROM dual
;
6 rows affected




CREATE TABLE bookingdetails
    ("BD_ID" int, "ACC_NO" int, "NAME" varchar2(5), "USER_ID" int)
;

INSERT ALL 
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1001, 1234, 'SBI', 1)
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1002, 5623, 'KVB', 5)
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1003, 9876, 'ICICI', 4)
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1004, 9193, 'HDFC', 2)
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1005, 8397, 'HDFC', 3)
    INTO bookingdetails ("BD_ID", "ACC_NO", "NAME", "USER_ID")
         VALUES (1006, 1234, 'SBI', 1)
SELECT * FROM dual
;
6 rows affected

select u.name,u.address
    from users u where exists 
    ( select 1 from bookingdetails b 
      where b.user_id=u.user_id
         and lower(b.name) != 'hdfc'
    ) order by u.name;


NAME
ADDRESS


Johan
Delhi

John
Bangalore

Krena
Mumbai