CODEWITHSUNDEEP
mysql
Aramil
Aramil

Reputation: 421

Mysql query to search between two tables with no 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

Upvotes: 3

Views: 172

Answers (2)

void
void

Reputation: 7890

I think this query should give you the expected result:

SELECT P.Person_name AS Person, COUNT(F.id) AS fixes 
FROM works W
INNER JOIN fixes F ON W.home_work_id = F.home_work_id and W.task_id=F.Task_id
INNER JOIN people P ON W.person_id = P.id
group by P.Person_name

however because I didn't read the question deeply, so I'm not sure 100% this will give exact result.

EDIT: question owner wrote the exact query of answer below.

Note from owner of the answer: Check the query, its based on it but not the "exact" query.

Upvotes: 1

Aramil
Aramil

Reputation: 421

Ok after do some tests and thanks to the comments from Farhang Amary and Rick James from dba.stackexchange, 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 tha can be fixed on 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;

Upvotes: 1

Related Questions