November 19th, 2013, 11:17 AM
Mysql how to get related tasks using a relationship table
I have three tables:
I need to select all the tasks related to a project which is easy but the project tasks are separated into milestones and subtasks. The sub tasks are stored in the same table as milestones but have their milestone flag set to 0. The relationship between milestones and sub-tasks is stored in projecttask_projecttask_1_c. So basically I need a query to select milestones and their related subtasks grouped by milestone so it looks like this:
and so on.
We only need the project id from the projects table and I am selecting * from the project_task table. projecttask_projecttask_1_c contains the following fields:
id | date modified | projecttask_projecttask_1projecttask_ida | projecttask_projecttask_1projecttask_idb
where projecttask_projecttask_1projecttask_idb is the id of the milestone task.
November 19th, 2013, 07:52 PM
Can you post the fields each table contain? (SHOW CREATE TABLE tablename)
Do you have any data example of the three tables and how you want the result to be?
Generally, you will have to use the JOIN to combine data from two tables:
Basic join could be something like this:
SELECT table1.datafield, table2.datafield
INNER JOIN table2 ON table2.id = table1.id