我无法简洁地描述我需要什么,因此感谢任何编辑标题的帮助!
我有 3 张表:
-
Jobs
(has JobID
PK)
-
JobsDetail
(has JobID
FK and DepartmentsID
FK)
-
Departments
(has DepartmentsID
PK)
Departments
记录数量较少 (~10)
如果我有 10 个部门行,我需要为每个部门重复所有 10 行Jobs
记录。不在其中的 DepartmentIDJobsDetail
该作业的记录将显示NULL
作业列中的值。与一个Jobs
记录,典型LEFT JOIN
按照我想要的方式工作:
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
产生以下结果:
Department JobIdentifier
310 NULL
320 NULL
430 NULL
450 NULL
460 NULL
500 NULL
530 1000
533 1000
534 1000
535 NULL
当然,当添加另一个 Jobs 记录时,我会看到以下内容:
Department JobIdentifier
310 2000
320 NULL
430 NULL
450 NULL
460 2000
500 NULL
530 1000
533 1000
534 1000
534 2000
535 NULL
我需要的是这样的:
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
如何才能做到这一点?
以下是表中的一些测试数据:
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