Geek asks and answers

Grouping SQL Results from the Union

Hey guys, I have a table in my database used so that users can follow each other.

The table is as follows: Unique identifier FollowerID FollowedUserID

FollowID is the identifier of the user who is following someone else.

FollowedUserID is the user identifier that the FollowID user is following

I would like to get a list of connections from this table based on a single user. My request should return all other user identifiers that the current user executes or maintains without overlapping.

So, we have a few entries here:

FollowerID   FollowedUserID
    1              2
    1              3
    4              1
    2              1

This would indicate that user 1 is following users 2 and 3, and users 2 and 3 are being followed by user 1 . On the other side of the spectrum, it shows that user 1 is followed by users 2 and 3.

What I would like to do is figure out the connections that user 1 has, so the request should return users: 2, 3 and 4 (user 1 is followed by users 2 and 3 and followed by user 4)

How can I achieve this from a single request?

A , - , , ( ). , .

UNION, :

SELECT FollowerID, FollowedUserID From Follows WHERE FollowerID = 1
UNION
SELECT FollowerID, FollowedUserID FROM Follows WHERE FollowedUserID = 1

FollowerID FollowedUserID , .

, php side - .

+3
2

, UNION:

SELECT FollowerID AS reference_user, FollowedUserID AS connection
  FROM Follows
 WHERE FollowerID = 1
UNION
SELECT FollowedUserID AS reference_user, FollowerID AS connection
  FROM Follows
 WHERE FollowedUserID = 1
GROUP BY reference_user;

, WHERE, .

+8

, :

edit: removed the un-needed select distinct
SELECT FollowedUserID as ID From Follows WHERE FollowerID = 1
UNION
SELECT FollowerID as ID FROM Follows WHERE FollowedUserID = 1

.

SELECT FollowedUserID as ID, "follow" as direction 
From Follows 
WHERE FollowerID = 1
UNION
SELECT FollowerID as ID, "followed" as direction 
FROM Follows 
WHERE FollowedUserID = 1

.

+2

More articles:


All Articles