在不同表的连接上获得错误的结果

Its a little bit complicated so i will gonna explain little slowly wont just right gonna jump in to the problem.

这有点复杂，所以我要慢慢地解释，我不会马上就开始讲这个问题。

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

我有一个数据库模式，在那里雇员被注册，那些雇员被分配工作在项目方面。

1 employee can work on many jobs, e-g employee John is on project abc on task 123 again employee John is also on project abc but on task 789

1员工可以做很多工作，e-g员工约翰在项目abc上在任务123上雇员约翰也在项目abc上但是在任务789上

the 456 task is assign to someone else. but thats another issue.

456任务分配给其他人。但这是另一个问题。

so we have John working on same project for different tasks, cuz of which i have two entries in my assign_job table.

所以我们让John为不同的任务做相同的项目，因为我在作业表中有两个条目。

Lets See the Image in little Schema below.

让我们看看下面的小图式中的图像。

This schema is fine. the problem i am now facing is that i want to have timeShedule tables. So i added Two Tables for Time Shedule.

这个模式很好。我现在面临的问题是我想要有时间表。所以我又加了两张桌子。

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. 招聘与选拔
3. Training & Development
4. 培训与发展

for project 1 i added 31 entries means for every day there is some time of worked the employee has done. but for project 2 the employee worked only 1 day.

对于项目1，我增加了31个条目，意味着员工每天都要做一些工作。但是对于项目2，这个员工只工作了1天。

Now i ran this query which i think is not the right way to create joins in this situation. cuz of which 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 where as i am getting 2 as Project ID.. May be there are more issues. please tell what is it i am doing wrong in these joins??

正如你在屏幕截图中看到的，我得到了项目id 1的项目名，我得到了项目id 2。可能还有更多的问题。请告诉我在这些连接中做错了什么?

============================

= = = = = = = = = = = = = = = = = = = = = = = = = = = =

Update

更新

SQLFIDDLELINK: click on heading or copy from below.

SQLFIDDLELINK:单击标题或从下面复制。

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

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

===========================我想要单独的记录，因为我想在表中显示它，如下所示。

3 个解决方案

#1

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.

您的时间表表或任何您想要命名的表，只需要一个外键—job_id，它指的是assign_job_id，以及在该作业上花费的时间。而且employee_id或project_id没有键，因为assign_job已经有了这个信息。我不想为你写代码，因为我知道你可以自己做。

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

你加入你的工时表到assign_job，然后加入到员工和项目中去。然后，您可以按员工或/以及项目或/和任务分组。这都是有

#2

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`

#3

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