How do I convert this Access Query to mySQL query

Question

This question is continuation of the following question: How do I convert this Access Query to mySQL query?

I want to represent the above in my code as follows:

the Access SQL FROM statement is as below

FROM qryvw_employees 
INNER JOIN ((Tbl_Clients INNER JOIN (Tbl_Assignments 
INNER JOIN Tbl_Tasks ON Tbl_Assignments.Assignment_ID = Tbl_Tasks.Assignment_ID) ON Tbl_Clients.PAN = Tbl_Assignments.PAN) 
INNER JOIN qryvw_subtasks ON Tbl_Tasks.TaskID = qryvw_subtasks.TaskID) ON qryvw_employees.ID = Tbl_Tasks.Assigned_To

Based on the reply in this query I am trying this:

FROM 
qryvw_employees
INNER JOIN (tbl_clients 
INNER JOIN tbl_assignments ON tbl_clients.`PAN` = tbl_assignments.`PAN`
INNER JOIN tbl_tasks ON tbl_assignments.`Assignment_ID` = tbl_tasks.`Assignment_ID`
INNER JOIN qryvw_subtasks ON tbl_tasks.`TaskID` = qryvw_subtasks.`TaskID`) ON qryvw_employees.`ID` = tbl_tasks.`Assigned_To`

The diagram of the above relationship that I am trying to achieve is as follows: enter image description here

plz assist, do not delete on the basis of low reputation. this is genuinely proper doubt since two tables have a one to many relationship and this is not covered in the above question.

My question is how do i represent the two one to many relationship (as shown in diagram) in the SQL statement. This is continuation to the How do I convert this Access Query to mySQL query? link above.

Answer 1

I think your from clause will be like below

     FROM qryvw_employees INNER JOIN 
     ((Tbl_Clients INNER JOIN 
  (Tbl_Assignments INNER JOIN Tbl_Tasks 
  ON Tbl_Assignments.Assignment_ID = Tbl_Tasks.Assignment_ID)
   ON Tbl_Clients.PAN = Tbl_Assignments.PAN) 
  INNER JOIN qryvw_subtasks ON Tbl_Tasks.TaskID = qryvw_subtasks.TaskID)
  ON qryvw_employees.ID = Tbl_Tasks.Assigned_To

Answer 2

The FROM clause results in a list of subtasks multiplied with all clients that are related to the subtasks' assignments.

You can start with whichever table, it's up to you. Let's keep this the employee table. Then join the next table. The table related to the employees is the task table. Take this and join it on the employee ID. The next table to join can either be the subtasks table or the assignements table. Choose whichever. And so on, always add the next table to what you already have.

from qryvw_employees e
join tbl_tasks t on t.assigned_to = e.id
join qryvw_subtasks st on st.taskid = t.taskid
join tbl_assignments a on a.assignment_id = t.assignment_id
join tbl_clients c on c.pan = a.pan

If you don't select columns from all tables in your SELECT clause or if there is some aggregation in the query (SUM, COUNT, ..., GROUP BY, DISTINCT maybe?), then you may want to join derived tables (subqueries) or move tables to the WHERE clause for readability and/or performance. Without seeing what you are actually selecting, this is all advice I can give.

Answer 3

The query works in MySQL when the statement is as generated by Access iteself. No change required. Here is the answer below:

FROM 
qryvw_employees
INNER JOIN (tbl_clients 
INNER JOIN tbl_assignments ON tbl_clients.`PAN` = tbl_assignments.`PAN`
INNER JOIN tbl_tasks ON tbl_assignments.`Assignment_ID` = tbl_tasks.`Assignment_ID`
INNER JOIN qryvw_subtasks ON tbl_tasks.`TaskID` = qryvw_subtasks.`TaskID`) ON qryvw_employees.`ID` = tbl_tasks.`Assigned_To`

Answer 4

do the fact you are just using INNER query you should not use () and nested subquery ..

  select your_col1, your_col2, ..., your_colN

  FROM qryvw_employees 
  INNER JOIN Tbl_Assignments 
  INNER JOIN Tbl_Tasks ON Tbl_Assignments.Assignment_ID = Tbl_Tasks.Assignment_ID
  INNER JOIN Tbl_Clients ON Tbl_Clients.PAN = Tbl_Assignments.PAN
  INNER JOIN qryvw_subtasks ON Tbl_Tasks.TaskID = qryvw_subtasks.TaskID
  INNER JOIN  ON qryvw_employees.ID = Tbl_Tasks.Assigned_To

anyway in mysql each nested subquery should be named with proper alias eg: select from ( select .... from my_table) t1_alias