clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 541818 fiddles created (11245 in the last week).

CREATE TABLE FOLLOWERS (ID INT, FOLLOWER_ID INT, FOLLOWING_ID INT);
 hidden batch(es)


INSERT INTO FOLLOWERS VALUES ( 1, 1, 2 ), ( 2, 2, 1 ), ( 3, 1, 3 ), ( 4, 3, 2 ), ( 5, 3, 1 ), ( 6, 4, 1 ), ( 7, 5, 3 ), ( 8, 4, 2 ), ( 9, 5, 1 );
9 rows affected
 hidden batch(es)


CREATE TABLE USERS (USER_ID INT, NAME VARCHAR(16));
 hidden batch(es)


INSERT INTO USERS VALUES (1,'USER NAME 1'), (2,'USER NAME 2'), (3,'USER NAME 3'), (4,'USER NAME 4'), (5,'USER NAME 5');
5 rows affected
 hidden batch(es)


SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID FROM FOLLOWERS AS F LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID) WHERE F.FOLLOWING_ID = 1
user_id name following_id follower_id
2 USER NAME 2 1 2
3 USER NAME 3 1 3
4 USER NAME 4 1 4
5 USER NAME 5 1 5
 hidden batch(es)


SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID FROM FOLLOWERS AS F LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID) WHERE F.FOLLOWING_ID = 2
user_id name following_id follower_id
1 USER NAME 1 2 1
3 USER NAME 3 2 3
4 USER NAME 4 2 4
 hidden batch(es)


SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID, COUNT(CASE WHEN F.FOLLOWING_ID = 2 THEN 1 END) OVER (PARTITION BY F.FOLLOWER_ID) cnt FROM FOLLOWERS AS F LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID) WHERE F.FOLLOWING_ID IN (1,2)
user_id name following_id follower_id cnt
1 USER NAME 1 2 1 1
2 USER NAME 2 1 2 0
3 USER NAME 3 1 3 1
3 USER NAME 3 2 3 1
4 USER NAME 4 2 4 1
4 USER NAME 4 1 4 1
5 USER NAME 5 1 5 0
 hidden batch(es)


WITH cte AS (SELECT F.FOLLOWER_ID, F.FOLLOWING_ID, U.NAME, COUNT(CASE WHEN F.FOLLOWING_ID = 2 THEN 1 END) OVER (PARTITION BY F.FOLLOWER_ID) > 0 is_follower FROM FOLLOWERS AS F LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID) WHERE F.FOLLOWING_ID IN (1,2)) SELECT * FROM cte WHERE FOLLOWING_ID = 1
follower_id following_id name is_follower
2 1 USER NAME 2 f
3 1 USER NAME 3 t
4 1 USER NAME 4 t
5 1 USER NAME 5 f
 hidden batch(es)