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.
source to share
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
source to share
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`
source to share
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
source to share