MySQL Query 10 Tables (Sequelize or Raw Query)

Question

In order to return the following JSON example, we need to query 10 tables, while looking up values in between. My knowledge of SQL is limited, so we are here asking for help.

JSON:

{
  project: 1,
  name: "BluePrint1",
  description: "BluePrint 1 Description",
  listWorkPackages: [
    {
      id: 1,
      name: "WorkPackage 1 Name",
      description: "WorkPackage 1 Description",
      type: "WorkPackage Type",
      department: "WorkPackage Department",
      status: "Workpackage work status"
    },
    {
      id: 2,
      name: "WorkPackage 2 Name",
      description: "WorkPackage 2 Description",
      type: "WorkPackage Type",
      department: "WorkPackage Department",
      status: "Workpackage work status"
    }
  ],
  assignments: [
    {
      id: 3,
      name: "WorkPackage 3 Name",
      description: "WorkPackage 3 Description",
      type: "WorkPackage Type",
      department: "WorkPackage Department",
      status: "Workpackage work status"
    }
  ]
}

Database:

The database looks like this (open in new tab for more details) :

Logic:

With the WorkerID, we want All the WorkPackages that:

• Have the same Type as the Worker;
• And belong to the same Department;
• And also the ones by direct Assignment (via table WA_Assignments)

So we can sent the information present on the JSON, we need to look into these 10 tables:

1. WK_Worker
2. WT_WorkerType
3. TY_Type
4. WP_WorkPackage
5. WE_WorkPackageExecution
6. WS_WorkStatus
7. BL_Blueprint
8. PR_Project
9. DP_Department
10. WA_WorkAssignments

My Problem:

My knowledge of SQL is limited to JOIN's:

SELECT *
FROM BL_Blueprint 
JOIN PR_Project ON BL_idBlueprint = PR_idBlueprint
JOIN WP_WorkPackage ON BL_idBlueprint = WP_idBlueprint
JOIN WE_WorkPackageExecution ON WE_idWorkPackage = WP_idWorkPackage
JOIN DP_Department ON WE_idDepartment = DP_idDepartment

And we need to only search work packages that have the same Type as the Worker, but we don't know the types before hand, only after looking into the table WT_WorkerType.

I read about subQuery where you can SELECT in the WHERE field, but couldn't get my head around it, and get a working query.

Problems:

In the end, my problems are:

1. The SQL query;

I am using Sequelize, if it can help, but from the docs I think a Raw Query would be easier.

Thank you all for your help and support.

---

SOLUTION

All the love goes to @MihaiOvidiuDrăgoi (in the comments) that helped me arrive at the solution

The First SELECT gets the Assignments and the Second the logic describe above. The labels help to identify which is which, and we order to ease the creation of the JSON.

SELECT *
FROM
    ((SELECT 
        BL_Name,
            BL_Description,
            WP_Name,
            WP_Description,
            PR_idProject,
            WE_idWorkPackageExecution,
            WE_idWorkStatus,
            TY_TypeName,
            TY_Description,
            WS_WorkStatus,
            DP_Name,
            DP_Description,
            'second_select'
    FROM
        WK_Worker, WP_WorkPackage
    INNER JOIN BL_Blueprint ON BL_idBlueprint = WP_idBlueprint
    INNER JOIN PR_Project ON PR_idBlueprint = BL_idBlueprint
    INNER JOIN WE_WorkPackageExecution ON WE_idWorkPackage = WP_idWorkPackage
        AND WE_idProject = PR_idProject
    INNER JOIN WS_WorkStatus ON WS_idWorkStatus = WE_idWorkStatus
    INNER JOIN DP_Department ON DP_idDepartment = WE_idDepartment
    INNER JOIN WA_WorkAssignments ON WA_idWorkPackageExecution = WE_idWorkPackageExecution
    INNER JOIN TY_Type ON TY_idType = WP_idType
    WHERE
        WA_idWorker = 1 AND WK_idWorker = 1) UNION ALL (SELECT 
        BL_Name,
            BL_Description,
            WP_Name,
            WP_Description,
            PR_idProject,
            WE_idWorkPackageExecution,
            WE_idWorkStatus,
            TY_TypeName,
            TY_Description,
            WS_WorkStatus,
            DP_Name,
            DP_Description,
            'first_select'
    FROM
        WK_Worker, WP_WorkPackage
    JOIN BL_Blueprint ON BL_idBlueprint = WP_idBlueprint
    JOIN PR_Project ON PR_idBlueprint = BL_idBlueprint
    JOIN WE_WorkPackageExecution ON WE_idWorkPackage = WP_idWorkPackage
        AND WE_idProject = PR_idProject
    JOIN WS_WorkStatus ON WS_idWorkStatus = WE_idWorkStatus
    JOIN DP_Department ON DP_idDepartment = WE_idDepartment
    JOIN TY_Type ON TY_idType = WP_idType
    WHERE
        WK_idWorker = 1
            AND DP_idDepartment IN 
            (SELECT 
                WK_idDepartment
            FROM
                WK_Worker
            WHERE
                WK_idWorker = 1)
            AND WP_idType IN 
            (SELECT 
                TY_idType
            FROM
                TY_Type
            JOIN WT_WorkerType ON TY_idType = WT_idType
            WHERE
                WT_idWorker = 1)
    )
) AS T1
ORDER BY T1.PR_idProject

Answer 1

Since you seem to need a single resultset that is composed from two different joins, you should probably do something like

SELECT * from
(
SELECT 1 
UNION ALL 
SELECT 2 
) a
ORDER by ...

Both your select statements should also include the logic name as distinct columns (like 'firstselect') - this way you will know which select your row comes from.