Sql query to search for users with the same skills

Question

I have three tables

• UserInfo (U-id, U-name)
• Skill (S-ID, S-Name) and a bridge table between them (because they have many different relationships).
• UserSkill (U-ID, S-ID)

I want to write a query to find users with the same skills, for example, this is an example of data in the UserSkill table

U-id   S-id
1       1
1       2
1       7
2       1
2       6

therefore, the result will be as

UserName1    UserName2   SkillName
A              B            Java

ad is my request

{select ui.UserName,ui2.UserName,SkillName
  from 
  UserSkill us1 inner join UserSkill us2 
on us1.SkillID = us2.SkillID and us1.UserID <> us2.UserID
  inner join UsersINFO UI 
on ui.UserID = us1.UserID 
  inner join UsersINFO ui2 
on ui2.UserID = us2.UserID
  inner join Skill s 
on s.SkillID = us2.SkillID}

I want to know if anyone knows the best way to write a query

Answer 1

You just need to add Where ui.UserName<ui2.UserNameat the end

select ui.UserName,ui2.UserName,SkillName
  from 
  UserSkill us1 inner join UserSkill us2 
on us1.SkillID = us2.SkillID and us1.UserID <> us2.UserID
  inner join UsersINFO UI 
on ui.UserID = us1.UserID 
  inner join UsersINFO ui2 
on ui2.UserID = us2.UserID
  inner join Skill s 
on s.SkillID = us2.SkillID
Where ui.UserName<ui2.UserName

Output

UserName    UserName    SkillName
A           B           Java

Live demo

http://sqlfiddle.com/#!18/64540/1

Answer 2

The result from your query is just messy. It reports A, B, java and B, A, java.

declare @J table (uid int, sid int, primary key (uid, sid));
insert into @J values 
       (1, 1)
     , (1, 2)
     , (1, 7)
     , (2, 1)
     , (2, 6)
     , (3, 1)
     , (3, 2);
declare @N table (id int primary key, name varchar(10));
insert into @N values 
       (1, 'bob')
     , (2, 'ted') 
     , (3, 'mac');
select j1.sid, n1.name, n2.name
from @J j1
join @J j2 
  on j2.sid = j1.sid 
 and j2.uid <> j1.uid
join @N n1 
  on n1.id = j1.uid
join @N n2 
  on n2.id = j2.uid
order by j1.sid, j1.uid, j2.uid;

sid         name       name
----------- ---------- ----------
1           bob        ted
1           bob        mac
1           ted        bob
1           ted        mac
1           mac        bob
1           mac        ted
2           bob        mac
2           mac        bob

< > a > , , 2 .

select j1.sid, n1.name, n2.name
from @J j1
join @J j2 
  on j2.sid = j1.sid 
 and j2.uid > j1.uid
join @N n1 
  on n1.id = j1.uid
join @N n2 
  on n2.id = j2.uid
order by j1.sid, j1.uid, j2.uid;

sid         name       name
----------- ---------- ----------
1           bob        ted
1           bob        mac
1           ted        mac
2           bob        mac

select * 
from ( select j1.sid, n1.name 
            , count(*) over (partition by j1.sid) as cnt
         from @J j1
         join @N n1 
           on n1.id = j1.uid 
     ) t
where t.cnt > 1
order by t.sid, t.name;

sid         name       cnt
----------- ---------- -----------
1           bob        3
1           mac        3
1           ted        3
2           bob        2
2           mac        2

Answer 3

Listed below are all pairs of users who have the skill with this skill:

select ui.name, us2.name, s.s_name
from userskill us join
     userskill us2
     on us.sid = us2.sid join
     skills s
     on s.sid = us.sid join
     userinfo ui
     on ui.uid = us.uid join
     userinfo ui2
     on ui2.uid = us2.uid
where us.uid <> us2.uid;