How Do I Convert This Access Query To Mysql Query

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

Solution 1:

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
INNERJOIN (tbl_clients 
INNERJOIN tbl_assignments ON tbl_clients.`PAN` = tbl_assignments.`PAN`
INNERJOIN tbl_tasks ON tbl_assignments.`Assignment_ID` = tbl_tasks.`Assignment_ID`
INNERJOIN qryvw_subtasks ON tbl_tasks.`TaskID` = qryvw_subtasks.`TaskID`) ON qryvw_employees.`ID` = tbl_tasks.`Assigned_To`

Solution 2:

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 
  INNERJOIN Tbl_Assignments 
  INNERJOIN Tbl_Tasks ON Tbl_Assignments.Assignment_ID = Tbl_Tasks.Assignment_ID
  INNERJOIN Tbl_Clients ON Tbl_Clients.PAN = Tbl_Assignments.PAN
  INNERJOIN qryvw_subtasks ON Tbl_Tasks.TaskID = qryvw_subtasks.TaskID
  INNERJOINON 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

Solution 3:

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.

Solution 4:

I think your from clause will be like below

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