CODEWITHSUNDEEP
sqlpostgresqlone-to-many
fuyi
fuyi

Reputation: 2639

Single SQL SELECT query for one to many relationship

This question is hard to explain without a example.

I have 2 tables, companies and employees, they have one-to-many relationship, one Company can attend many Employees.

Simplest table structure is shown here

Company
| id | name |

Employee
| id | name | company_id | join_date |

Now the question is:

How could I select the first 2 employees and show their joining date as column in company table?

So the result looks like this

| id |  company_name |  first_employee_join_at |  second_employee_join_at |

Upvotes: 0

Views: 150

Answers (1)

user330315
user330315

Reputation:

Assuming there is a foreign key column company_id in the employee table:

with emps as (
  select id, name, company_id, 
         row_number() over (partition by company_id order by join_date) as rn
  from employee
)
select c.id, c.name as company_name, 
       e1.join_date as first_employee_join_at, 
       e2.join_date as second_employee_join_at
from company c
  left join emps e1 on e1.company_id = c.id and e1.rn = 1
  left join emps e2 on e2.company_id = c.id and e2.rn = 2;

This is not going to be terribly efficient though. A slightly more efficient version would use conditional aggregation:

with emps as (
  select id, name, company_id, 
         row_number() over (partition by company_id order by join_date) as rn
  from employee
)
select c.id, c.name as company_name, 
       max(e.join_date) filter (where rn = 1) as first_employee_join_at, 
       max(e.join_date) filter (where rn = 2) as second_employee_join_at
from company c
  join emps e on e.company_id = c.id and e.rn in (1,2)
group by c.id, c.name;

Upvotes: 2

Related Questions