many to many relation

Question

I will be receiving 3 excel files each week. The first are projects from department A, with a Project_ID an x amount of columns. The second are projects from department B, again with a Project_ID x amount of columns. These projects are often related (many to many relationships) thus I will receive a third excel with a mapping of which projects belong to which projects. Later this process will be automated, therefore I'd like to attempt to create a decent SQL solution in advance.

My output should show related projects together as follows:

<br />Department A | Project_ID | Project name | etc
<br />Department B | Project_ID | Project name | etc
<br />Department B | Project_ID | Project name | etc

In addition, there should be an indication when a project does not have a mapping (yet).

I have tried: Created a table for each excel file. Then full joined the Department A project to a project B so that all projects are in the table regardless of the link. However this result shows the related projects next to each other, not below. To further clarify I have created a small example in excel.The tabs represent a file, the mapping or the output.

Hope you'll be able to give me the right push! Thanks

Answer 1

It seems as though you'd like to use union all, not join:

SELECT 'Department A', id, name
FROM   projects_a
UNION ALL
SELECT 'Department B', id, name
FROM   projects_b
UNION ALL
SELECT 'Department C', id, name
FROM   projects_c
UNION ALL
SELECT 'Department D', id, name
FROM   projects_d