Getting incorrect results when joining to different tables
Its a bit more complicated, so I'll go a little slow, just not going into the problem.
I have a database schema where employees are registered, these employees are assigned jobs from a project perspective.
1 employee can work on many jobs, eg the employee John
is on project
abc
the task 123
again the employee is John
also on project
abc
, but on the task789
the task 456
is assigned to someone else. but this is another problem.
so we are working on the same project for different tasks, because I have two entries in my assign_job table.
See the image in the small diagram below.
This circuit is fine. the problem I am currently facing is that I want to have time tables. So I added two tables for the time schedule.
for which the new circuit will be below
I added some Dummy Entries to two new tables for the two projects listed below.
- Selection and selection
- Education and development
for project 1, I added 31 items of funds for each day the employee worked for a while. but for project 2 the employee only worked for 1 day.
Now I ran this query which I think is not appropriate for creating joins in this situation . cuz from which I am posting this question as I am getting incorrect 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 the next Shot screen I am getting the project name for the project id 1, where as I am getting 2 as the project id. Maybe there are more problems. tell me what am I doing wrong in these unions?
=============================
Update
SQLFIDDLELINK: Click on the title or copy below.
http://sqlfiddle.com/#!2/dc4585/1
============================= I want the individual records, since I want to show them inside the table, to look like below.
timesheet
Only one foreign key is required in the table, or anywhere you want to name it - job_id
which refers to assign_job_id and the hours spent on that job. And no clues for employee_id
or project_id
because we assign_job
already have this information. I don’t want to write code for you because I see that you can do it yourself.
You join your schedule for assign_job and join that employee and project - voila. Then you can group the worker or / or by project and / or task. it's all there
Try this:
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`
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, tough 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