Getting wrong results on joins in different tables

Question

I have database Schema where employees are registered, those employees are assigned jobs in terms of project.

1 employee can work on many jobs, eg. employee John is on project abc on task 123. Employee John is also on project abc but on task 789.

The 456 task is assign to someone else, but that's another issue.

So we have John working on same project for different tasks, because of which I have two entries in my assign_job table.

Let's see the image in little schema below:

This schema is fine. The problem I am now facing is that I want to have timeSchedule tables. So I added two tables for Time Schedule.

To which new schema would be below:

I added some dummy entries to the two new tables for two projects as listed below.

1. Recruitment & Selection
2. Training & Development

For project 1, I added 31 entries i.e. for every day there is some time of worked the employee has done. But for project 2 the employee worked only 1 day.

Now I ran this query which I think is not the right way to create joins in this situation. I am posting this question as I am getting the wrong results for the data in the database.

SELECT 
TS.`date_created` AS TimeSheetMonth, 
TSD.`project_id` AS ProjectID, 
TSD.`date` AS WorkDate, 
TSD.`hours` AS WorkHours, 
TS.`employee_id` AS EmployeeID,
E.`full_name` AS EmployeeName,
MLP.`project_title` AS ProjectTitle,
TS.`id` AS TimeSheetID
FROM timesheet TS
INNER JOIN timesheet_details TSD
ON TS.`id`= TSD.`timesheet_id`
INNER JOIN employee E
ON E.`employee_id` = TS.`employee_id`
INNER JOIN assign_job AJ
ON AJ.`employee_id` = E.`employee_id` AND AJ.`trashed` = 0
INNER JOIN ml_projects MLP
ON AJ.`project_id` = MLP.`project_id`
INNER JOIN ml_projects TMLP
ON TMLP.`project_id` = TSD.`project_id`
WHERE TS.`id`=1
GROUP BY TSD.`timesheetDetails_id`, TSD.`project_id`

As you can see in below screen shot I am getting the project name for project id 1 whereas I am getting 2 as Project ID. Maybe there are more issues. What am I doing wrong in these joins?

Update

SQL Fiddle

http://sqlfiddle.com/#!2/dc4585/1

I want individual records as I want to show it inside the table look like below one.

Answer 1

Do you just Need something like this:

SELECT 
    concat(TS.`employee_id`, " - ", E.`full_name`) as employee,
    concat(TSD.`project_id`, " - ", MLP.`project_title`) as Project,
    TS.`date_created` AS TimeSheetMonth,
    sum(1) as WorkDaysOnProjectInMonth,
    sum(TSD.`hours`) AS WorkHoursOnProjectInMonth

FROM
    timesheet TS
    INNER JOIN timesheet_details TSD ON TS.`id`= TSD.`timesheet_id`
    INNER JOIN employee E ON E.`employee_id` = TS.`employee_id`
    INNER JOIN assign_job AJ ON AJ.`employee_id` = E.`employee_id` 
    INNER JOIN ml_projects MLP ON AJ.`project_id` = MLP.`project_id`
WHERE 
    TS.`id`=1
    AND AJ.`trashed` = 0
GROUP BY
        1,2

EDIT

Wow, hard question! But I think this solves your Problem:

SELECT 
    aj.PROJECT_ID,AJ.`employee_id`,
    mlp.project_title,
    e.full_name,
    TS.id,
    TSD.date, TSD.hours

FROM
   assign_job AJ 
    INNER JOIN ml_projects MLP ON AJ.`project_id` = MLP.`project_id` -- P:1
    INNER JOIN employee E ON AJ.`employee_id` = E.`employee_id`
    INNER JOIN timesheet TS ON E.`employee_id` = TS.`employee_id`
    INNER JOIN timesheet_details TSD ON TS.`id`= TSD.`timesheet_id`

WHERE 
    TS.`id`=1
    AND AJ.`trashed` = 0

Answer 2

Your timesheet table or whatever your want to name it, needs only one foreign key - job_id that refers to assign_job_id and hours spent on that assignment. And no keys for employee_id or project_id because the assign_job already has that info. I don't want to write the code for you because i can see that you can do it yourself.

You join your timesheet to assign_job and join that one to employee and project - voila. You can then group by employee or/and by project or/and task. it's all there

Answer 3

Try it this way;

SELECT 
    TSD.`project_id` AS ProjectID, 
    TSD.`date` AS WorkDate, 
    TSD.`hours` AS WorkHours, 
    TS.`date_created` AS TimeSheetMonth,
    TS.`employee_id` AS EmployeeID,
    TS.`id` AS TimeSheetID,
    E.`full_name` AS EmployeeName,
    MLP.`project_title` AS ProjectTitle
FROM
    timesheet TS
    INNER JOIN timesheet_details TSD ON TS.`id`= TSD.`timesheet_id`
    INNER JOIN employee E ON E.`employee_id` = TS.`employee_id`
    INNER JOIN assign_job AJ ON AJ.`employee_id` = E.`employee_id` 
    INNER JOIN ml_projects MLP ON AJ.`project_id` = MLP.`project_id`
    -- INNER JOIN ml_projects TMLP ON TMLP.`project_id` = TSD.`project_id` -- what for?
WHERE 
    TS.`id`=1
    AND AJ.`trashed` = 0
GROUP BY 
    TSD.`timesheetDetails_id`, TSD.`project_id`