CODEWITHSUNDEEP
mysqlsqlcombinationspermutation
user5529778
user5529778

Reputation:

Permutation in SQL

I've got the following problem and can't find a solution (even after searching the web a lot).

I have a table with projects and the related employees (around 100.000 entries): 

+------------+-------------+  
| Project ID | Employee ID |  
+------------+-------------+  
| 1          | 1           |  
| 1          | 2           |  
| 1          | 3           |  
| 2          | 1           |  
| 2          | 2           |  
| 2          | 3           |  
| 3          | 1           |  
| 3          | 2           |  
| ...        | ...         |

Is it possible to find all distinct combinations of two employees per project and write it in a new table?

Desired output: 

Project ID | Employee ID 1 | Employee ID 2  
1          |1              |2  
1          |1              |3  
1          |2              |3  
2          |1              |2  
2          |1              |3   
...

Thanks!

Upvotes: 0

Views: 235

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146409

Yes, try this... 

  Select distinct Coalesce(a.ProjectId, b.ProjectId) projectId,
         a.EmployeeId, b.EmployeeId
  From table a  full join table b
      on a.ProjectId = b.ProjectId
         and b.EmployeeId > a.EmployeeId

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You do this using a self join:

select pe1.projectid, pe1.employeeid as employeeid1, pe2.employeeid as employeeid2
from pe pe1 join
     pe pe2
     on pe1.projectid = pe2.projectid and pe1.employeeid < pe2.employeeid;

Use insert or create table as to put this into a new table.

Upvotes: 3

Related Questions