CODEWITHSUNDEEP
sqlsql-servert-sql
PTTT
PTTT

Reputation: 251

Sql query to find users with same skills

I have three tables

  1. UserInfo (U-id,U-name)
  2. Skill(S-id,S-Name) and a bridge table between them (cause they have many to many relationship)
  3. UserSkill(U-id,S-id)

I want to write a query to find users with same skills for example this is a sample data in UserSkill table

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

so the result would be like this 

UserName1    UserName2   SkillName
A              B            Java

enter image description here

ad this is my query

{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 whether anyone knows a better way to write the query

Upvotes: 5

Views: 666

Answers (4)

abhishek khanchi
abhishek khanchi

Reputation: 1

I suggest not to have in pairs like above below is a sol : Here in below soln.. I am joining data and just grouping users by skill id and skillname.

SELECT
    o.skillname,
    LISTAGG(o.username, ',') WITHIN GROUP(
        ORDER BY
            o.username ASC
    ) user_common_skill
FROM
    (
        SELECT
            t.username,
            t.userid,
            t.skillid,
            s.skillname
        FROM
            (
                SELECT
                    ui.username,
                    ui.userid,
                    us.skillid
                FROM
                    userskill   us
                    INNER JOIN usersinfo   ui ON us.userid = ui.userid
            ) t
            LEFT JOIN skill s ON t.skillid = s.skillid
    ) o
GROUP BY
    o.skillid,
    o.skillname

Results of above query

Results of above query

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

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

Even if you replace <> with > it is still messy when you have more than 2 with the same skill.

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

I suggest you NOT report as pairs 

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The following returns all pairs of users that have a skill in common, along with that 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;

Upvotes: -1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

You just need to add Where ui.UserName<ui2.UserName at 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

Upvotes: 3

Related Questions