Argthtjtr

I have a friend Request table which holds a friend request data between two users (recipient and sender) and also the friendrequeststatus (ACCEPTED, REJECTED):

i want to retrieve the friends of the User with id of 1, which include 2, 3, 4 and if possible for them by order of name.

By friend i mean (where a user e.g 1, is either a sender or recipient to another user e.g 2 and the friendrequeststatus has the value of ACCEPETED). By friends i mean all columns where the user e.g 1 whether as a recipient or sender has an accepted friendrequeststatus.

I tried
`

 select request.*

from friendrequest request

where 1 in (request.recipient, request.sender) 

Setup:

CREATE TABLE t (id INTEGER, status TEXT, recipient INTEGER, sender INTEGER);



INSERT INTO t

VALUES

(1, 'ACCEPTED', 2, 1),

(2, 'ACCEPTED', 3, 1),

(3, 'ACCEPTED', 1, 4),

(4, 'REJECTED', 5, 1),

(5, 'REJECTED', 1, 5),

(6, 'ACCEPTED', 6, 7),

(7, 'ACCEPTED', 1, 2);

Query:

SELECT DISTINCT CASE sender WHEN 1 THEN recipient ELSE sender END AS friends

FROM t

WHERE 1 IN (recipient, sender)

AND status = 'ACCEPTED'

ORDER BY 1

Results:

| friends |

| ------- |

| 2       |

| 3       |

| 4       |

https://www.db-fiddle.com/f/sfbFeXCvWkoSagJEipfxSr/0

So firstly I've added some more data to prove only the correct data is used. The query checks recipient/sender for 1, as you did in your query. It also checks the status field for 'ACCEPTED', and then it creates a single column using the value which is not 1 (the CASE statement) to the the friend value form the correct column. It then does a DISTINCT to make sure the same person isn't reported twice (if they're a sender in one record and a recipient in another... if that's even possible). The it orders by that generated column.