#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Location
    Scotland
    Posts
    109
    Rep Power
    7

    Mysql how to get related tasks using a relationship table


    I have three tables:

    projects,

    project_tasks,

    projecttask_projecttask_1_c

    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:

    Milestone

    subtask

    subtask

    subtask

    Milestone

    subtask

    subtask

    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.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,692
    Rep Power
    1958
    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:
    Code:
    SELECT table1.datafield, table2.datafield
    FROM table1
    INNER JOIN table2 ON table2.id = table1.id

IMN logo majestic logo threadwatch logo seochat tools logo