Repeat all base table rows for EACH left join row match

Question

I am having trouble succinctly describing what I need, so any help editing the title is appreciated!

I have 3 Tables:

1. Jobs (has JobID PK)
2. JobsDetail (has JobID FK and DepartmentsID FK)
3. Departments (has DepartmentsID PK)

Departments will have a low number of records (~10)

If I have 10 Department rows, I need all 10 rows repeated for each Jobs record. DepartmentIDs that aren't in the JobsDetail records for the Job will show a NULL value in the Job column. With one Jobs record, a typical LEFT JOIN works the way I want it to:

Select d.Department, jobs.JobIdentifier
From    Departments d 
LEFT JOIN (Select Distinct j.JobID, j.JobIdentifier, DepartmentID, 
           From   Jobs j
           Join   JobsDetail jd on j.JobID = jd.JobID) jobs on d.DepartmentID = jobs.DepartmentID

yields these results:

Department  JobIdentifier
310         NULL
320         NULL
430         NULL
450         NULL
460         NULL
500         NULL
530         1000
533         1000
534         1000
535         NULL

Of course, when another Jobs record is added, I see this:

Department  JobIdentifier
310         2000
320         NULL
430         NULL
450         NULL
460         2000
500         NULL
530         1000
533         1000
534         1000
534         2000
535         NULL

What I NEED is something like this:

Department  JobIdentifier
310         NULL
320         NULL
430         NULL
450         NULL
460         NULL
500         NULL
530         1000
533         1000
534         1000
535         NULL
310         2000
320         NULL
430         NULL
450         NULL
460         2000
500         NULL
530         NULL
533         NULL
534         2000
535         NULL

How can this be accomplished?

Here is some test data from the tables:

JobID                SubPlantID  JobIdentifier                                      PartFamilyID OrderDate               OrderedBy                                          OrderQuantity DueDate                 SpecialInstructions                                                                                                                                                                                                                                              PrintDate               PrintedBy                                          StartDate               StartedBy                                          ProducedQuantity ReprintNumber CompletedDate           Location
-------------------- ----------- -------------------------------------------------- ------------ ----------------------- -------------------------------------------------- ------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ---------------- ------------- ----------------------- --------------------------------------------------
2                    1           1000                                               1            2012-12-21 13:20:00.000 Keith                                              1             2012-12-28 00:00:00.000                                                                                                                                                                                                                                                                 NULL                    NULL                                               2012-12-28 00:00:00.000 NULL                                               NULL             0             NULL                    NULL
3                    1           2000                                               1            2013-01-03 00:00:00.000 Jon                                                10            2013-01-10 00:00:00.000 NULL                                                                                                                                                                                                                                                             NULL                    NULL                                               NULL                    NULL                                               NULL             0             NULL                    NULL

JobsDetailID         JobID                Operation FirstStartDate          OperationQuantity OperationStatusTypeID OperationDescription                                                                                                                                                                                                                                           DepartmentID WorkCenterID UserName
-------------------- -------------------- --------- ----------------------- ----------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ --------------------------------------------------
1                    2                    10        NULL                    1                 2                     Weld                                                                                                                                                                                                                                                           1            2            keith
2                    2                    20        NULL                    1                 3                     Punch                                                                                                                                                                                                                                                          1            1            keith
3                    2                    30        NULL                    1                 2                     Form                                                                                                                                                                                                                                                           3            5            jon
4                    2                    40        NULL                    1                 3                     Paint                                                                                                                                                                                                                                                          2            4            jon
6                    2                    50        NULL                    1                 3                     Glue                                                                                                                                                                                                                                                           2            4            jwood
9                    2                    60        NULL                    1                 2                     Eat                                                                                                                                                                                                                                                            3            5            jon
10                   3                    20        NULL                    10                1                     Punch                                                                                                                                                                                                                                                          10           18           Jon Wrye
11                   3                    30        NULL                    10                0                     Form                                                                                                                                                                                                                                                           15           29           Jon Wrye
12                   3                    40        NULL                    10                0                     Weld                                                                                                                                                                                                                                                           13           24           Jon Wrye
13                   3                    10        NULL                    10                2                     Start                                                                                                                                                                                                                                                          1            1            jwrye
14                   3                    50        NULL                    10                0                     Finish                                                                                                                                                                                                                                                         1            2            jwrye

DepartmentID Department
------------ --------------------------------------------------
1            534
2            533
3            530
4            535
5            500
6            450
7            430
8            200
9            240
10           232
11           220
12           300
13           460
14           320
15           310

Answer 1

You should start with a cross join between jobs and departments, and then build the query from there:

Select d.Department, jobs.JobIdentifier
From    Departments d cross join
        (select distinct JobIdentifier from Jobs j) ji LEFT JOIN
        (Select Distinct j.JobID, j.JobIdentifier, DepartmentID, 
         From Jobs j Join
              JobsDetail jd
              on j.JobID = jd.JobID
        ) jobs
        on d.DepartmentID = jobs.DepartmentID and
           ji.jobidentifer = jobs.jobIDentifier

I'm a bit unclear on the distinction between JobId and JobIdentifier, so this may not be quite right.

If you add ji.JobIdentifier to the select list, you'll see which job the department should belong to, even when there is no match.