Mysql – Get rows with minimum value

aggregatejoin;MySQLselect

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.

Best Answer

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.

Related Solutions

Mysql – Help with modeling this table

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table. If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select * from phone as p join employee as e on e.employee_id = p.employee_id where e.company_id = 1

I hope this helps :)

Mysql – Peer review database design for various reports

I think you are doing well going for the 4th normal form and exploding your data. Now, as you found out, you need to de-normalise to support for historical data support: if the review will always account for a particular department's score whatever the current department of the reviewee_id is, then yes, you definitely need to add a department at the review table and make sure you know what/why it is there for.

Push the logic further: what if there is a 4th and 5th category added in a year? How do you calculate past and future averages then? On 3 or 5 categories? Then another year later, the first category gets removed? Will you delete all scores for that category, or change yet again the average's calculation based on the number of "category" at the time of the "date" of the "score"?

Sometime, too much normalisation is not the best course of action. You have a good core here. Time to custom-fit now. Take a step back, plan for the future, ask the end-users to make sure you support all their foreseeable needs, and look at the impacts of adding/removing/updating departments, employees and categories on historical reporting.

As for reporting, if you are using powerful data tools like IBM Cognos, it will be easy to generate data cubes, then let managers do their own reports. If you are using tools like Microsoft Excel with "external data", then the easiest course of action would be to pre-create Views or Stored Procedures that crunch up the data. The caveat here is that you will have to create a new View/SP for every manager that comes up with a new request/idea/variant.

Related Question