1

OK i found myself in a dead end, and i know must be a way but my brain is just about to explode. This is the case: I have two tables with tons of rows, one for works done (lets call it works table), something like this:

ID | Home_Work_ID | task_id | Person_id
1  | 23           | 1       | 30
2  | 23           | 2       | 31
3  | 23           | 3       | 30
4  | 876          | 1       | 31
5  | 123          | 3       | 32

and another table to report the fixes to do on the works mentioned before, lets call it fixes table

ID | Home_Work_ID | Person_reporting_id | Task_id | Details
1  | 23           | 93                  | 1       | Fix this
2  | 23           | 85                  | 3       | Fix that
3  | 123          | 86                  | 3       | Fix something

As we can see, in the fixes table there are home works with fixes reported, and those home works was done by the person_id 30 and 32 (from the work table). The results that im trying to achieve would be:

Person                               | Fixes 
John (lets say this is person id 30) | 2
Kate (lets say this is person id 32) | 1

The problem is, i need to create a report that show me who was the person responsable of the work done in the works table that have reported fixes in the second table and how many fixes was reported for that person. The only link is the home work id and probably the task id, so i was testing something like this

SELECT P.person_name AS Person, COUNT(F.id) AS fixes 
FROM fixes F 
INNER JOIN homeworks H ON F.home_work_id = H.id 
INNER JOIN works as W 
INNER JOIN people AS P ON W.person_id = P.id 
INNER JOIN tasks AS T ON T.task_id = F.task_id
WHERE F.task_id = W.task_id 
AND F.home_work_id = W.home_work_id 
AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id) 
GROUP BY P.person_name ORDER BY fixes DESC

Ok there are three more inner/left joinable tables with the id and name of the person, home work and task. This show me the person responsable but the number of fixes for that person and that home_work/task dont match the ones in the fixes table, i guess im doing wrong inner joining the work table that way but i dont see the light yet. Any help will be really appreciated.

Regards

1

The COUNT is too high, correct? That is because the JOINs get more records before the GROUP BY comes along. I suggest you remove the ORDER BY and SELECT more fields so you can see which rows are coming redundantly. That may lead to splitting it into

SELECT ...
FROM ( SELECT person, COUNT(...) FROM ... JOIN ... ) X
JOIN some other tables

AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)

optimizes very poorly; change it to yet another JOIN. Why do you have the GROUP BY there? Perhaps you meant DISTINCT? Perhaps in this case, do

JOIN ( SELECT DISTINCT home_work_id FROM fixes ) Q ON Q.home_work_id = W.home_work_id

  • Yes the count is bigger, ill try that and see what it comes out. The group by is because one person can have several fixes reported. What im after is to know, how many fixes was reported for the work of that person as a way to measure the efficiency – Aramil Jan 30 '15 at 5:08
1

Ok after do some tests and thanks to the comments from Rick James and Farhang Amary from stackoverflow, the following query seems to do the job, so im posting it here for anyone that find itself on the same situation or for anything wrong with it:

SELECT P.Person_name AS Person, COUNT(F.id) AS fixes
FROM (SELECT person_id, home_work_id, task_id FROM work) W
  LEFT JOIN fixes_table F ON W.home_work_id = F.home_work_id AND W.task_id = F.taks_id
  LEFT JOIN People_table P ON W.person_id = P.id
GROUP BY P.person_name ORDER BY fixes DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.