SQL SELECT composite primary key

I have 2 tables User1 and Relationship and it’s some kind of user-friends relationship. I need to find all the friends of a user.

For example: I have a user with ID=3 and I need to find all the users who have Relationship.STATUS = 1 with this user.

FYI, I’m a beginner in SQL, so I know it’s quite simple task but I can’t handle it.

I’ve tried to use JOIN but it wasn’t successfully.

SELECT *
FROM USER1
         RIGHT JOIN RELATIONSHIP R on USER1.USER1_ID = R.USER_ID_FROM OR USER1.USER1_ID = R.USER_ID_TO
WHERE R.USER_ID_FROM = :id
   OR R.USER_ID_TO = :id AND R.STATUS = :status AND USER1_ID != :id;

My tables:

TABLE USER1
(
    USER1_ID     NUMBER PRIMARY KEY,
    USER_NAME    NVARCHAR2(64),
    REAL_NAME    NVARCHAR2(64),
    EMAIL        NVARCHAR2(64),
    PHONE_NUMBER NVARCHAR2(64),
    BIRTH_DATE   TIMESTAMP,
    POST_ID      NUMBER,
    PASSWORD     NVARCHAR2(16)

);

TABLE RELATIONSHIP
(
    USER_ID_FROM NUMBER NOT NULL,
    USER_ID_TO   NUMBER NOT NULL,
    STATUS       SMALLINT DEFAULT 0,
    CONSTRAINT FK_USER_ONE FOREIGN KEY (USER_ID_FROM) REFERENCES USER1 (USER1_ID),
    CONSTRAINT FK_USER_TWO FOREIGN KEY (USER_ID_TO) REFERENCES USER1 (USER1_ID),
    CONSTRAINT PK_RELATIONSHIP PRIMARY KEY (USER_ID_FROM, USER_ID_TO)

);

Answer

If you just want the ids then you can use conditional logic to which which of the user ids you want:

select (case when user_id_from = 1 then user_id_to else user_id_from
        end) as other_user_id
from relationship r
where 1 in (user_id_from, user_id_to) and
      status = 3;

If you actually want all the user information, I would suggests exists:

select u.*
from user1 u
where exists (select 1
              from relationship r
              where r.status = 3 and
                    ( (r.user_id_from = 1 and r.user_id_to = u.id) or
                      (r.user_id_to = 1 and r.user_id_from = u.id)
                    )
             );