3

I have tables company:

    company_id |  
- - - - - - - - - - - - - -  
      1        |               
      2        |               

employee:

employee_id | 
- - - - - - - - - - - -
      1     |        
      2     |        

They are connected via table employee_company:

employee_id | company_id
- - - - - - - - - - - -
      1     |     1   
      2     |     1
      1     |     2
      2     |     2

Also there is some factor, connected to company via table:

factor_id | company_id
- - - - - - - - - - - -
    1     |     1   
    1     |     2
    2     |     3

Also there is score table, and for each score there is unique pair of company and employee:

score_id | employee_id | company_id | score | 
- - - - - - - - - - - - - - - - - - - - - - - -
   1     |     1       |      1     | 21
   2     |     1       |      2     | 30
   3     |     2       |      1     | 50
   4     |     2       |      2     | 11

The goal is to get table having set of rows presenting min score for each employee, filtered by factor, something like that:

  | employee_id | score | 
 - - - - - - - - - - - - -
        1       |  21
        2       |  11

Now I built query joining all tables and showing all scores:

select * from employee 
join employee_company on employee.employee_id=employee_company.employee_id 
join company on company.company_id=employee_company.company_id 
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id 
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;

How to reduce it to what I need? Thank you.

0
select employee_id, min(score) from 
employee 
join employee_company on employee.employee_id=employee_company.employee_id 
join company on company.company_id=employee_company.company_id 
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id 
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id = factor_id

group by employee_id

In the above statement, you take all employee ids and their scores from the query you gave, and take the minimum score for each employee ID. Adapt as needed if it's not exactly that that you want.

1

Try this query:

select * from employee join employee_company on    
employee.employee_id=employee_company.employee_id 
join company on    
company.company_id=employee_company.company_id 
join (select 
employee_id, 
company_id, 
min(score) score from score 
group by employee_id, 
company_id) 
score on score.employee_id=employee.employee_id and 
score.company_id=company.company_id 
join factor_company on 
factor_company.company_id=company.company_id 
join factor on 
factor_company.factor_id=factor.factor_id 
where factor.factor_id =:factor_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.