CODEWITHSUNDEEP
mysqlsql
Peter Jurkovic
Peter Jurkovic

Reputation: 2896

Query to select: who two employee works for the same company

Suppose that we have following tables:

company             company_has_employee        employee
-------------------------------------------------------------
id                  company_id                  id  
companzy_name       employee_id                 emplyee_name

How to create SQL Query, which retrieves any two employees, who works for the same company and what is this company?

Upvotes: 0

Views: 573

Answers (2)

Swagata
Swagata

Reputation: 622

Assuming juergen d's joins of the table are correct, I will modify the query to 

select top 2 company_name, e.employee_name
   from compyny c
   join company_has_employee ce on ce.company_id = c.id
   join employee e on e.id = cs.employee_id
   group by company_name
   having count(e.id) > 1

This will always return the top 2 employees

juergen d's original query will always return the first and last employees based on their ID.

If you want two employees chosen randomly, then you can try this:

select top 2 company_name, e.employee_name
   from compyny c
   join company_has_employee ce on ce.company_id = c.id
   join employee e on e.id = cs.employee_id
   group by company_name
   having count(e.id) > 1
   order by RAND((e.ID)*DATEPART(millisecond, GETDATE()))

The last order by clause will change the order of records randomly and you will always get the top 2 of a random order...which means 2 random employees will be selected each time the query is run.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

select company_name, 
       min(e.employee_name) as emp1, 
       max(e.employee_name) as emp2
from compyny c
join company_has_employee ce on ce.company_id = c.id
join employee e on e.id = cs.employee_id
group by company_name
having count(e.id) > 1

Upvotes: 1

Related Questions