jeudi 29 janvier 2015

Select from two tables with not direct relationship


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





Aucun commentaire:

Enregistrer un commentaire