CODEWITHSUNDEEP
sqlpostgresql
Oto-obong Eshiett
Oto-obong Eshiett

Reputation: 1679

Selecting a list of data which can contain values from 2 columns

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

enter image description here

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)

enter image description here

Upvotes: 1

Views: 62

Answers (1)

404
404

Reputation: 8572

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.

Upvotes: 1

Related Questions