SQL Query to find out common relationships from a given set of data-CodePudding

Table: friendship

userid_1	userid_2
1	4
2	7
4	6
3	4
5	7
6	7

From the above table, i want to find the count of relationships(userid_2) that are common for userid_1. Below is the sample output.

Output:

userid_1	common_userid
1, 3	4
2,5,6	7

CodePudding user response：

If your dbms is sql server or postgresql then you can use string_agg() with group by clause

Schema and insert statements:

 create table users(userid_1 int, userid_2 int)
 insert into users values(1,    4);
 insert into users values(2,    7);
 insert into users values(4,    6);
 insert into users values(3,    4);
 insert into users values(5,    7);
 insert into users values(6,    7);

Query for sql server:

 select string_agg(userid_1,',') userid_1,userid_2 common_userid 
 from users
 group by userid_2
 having count(userid_1)>1

Output:

userid_1	common_userid
1,3	4
5,6,2	7

db<>fiddle here

Query for postgresql:

 select string_agg(userid_1::varchar,',' order by userid_1) userid_1,userid_2 common_userid 
 from users
 group by userid_2
 having count(userid_1)>1

Output:

userid_1	common_userid
1,3	4
2,5,6	7

db<>fiddle here

CodePudding user response：

you can do something like this if you are using MySQL

SELECT GROUP_CONCAT(userid_1) as userid_1, userid_2 as common_userid FROM TableName GROUP BY userid_2

if you can specify the database, I might be able to write you even an specific sql

CodePudding user response：

Since you already got answers for MYSQL, Postgres and SQL Server DB, let's add a query for Oracle DB, too. You can use LISTAGG for that:

SELECT LISTAGG(userid_1,',')  AS userid_1,
userid_2 AS common_userid
FROM friendship
GROUP BY userid_2
HAVING COUNT(userid_1)>1
ORDER BY userid_1

The sorting can also be applied using WITHIN GROUP instead of at the end of the query:

SELECT LISTAGG(userid_1,',') 
WITHIN GROUP (ORDER BY userid_1) AS userid_1,
userid_2 AS common_userid
FROM friendship
GROUP BY userid_2
HAVING COUNT(userid_1)>1

db<>fiddle