对每个左连接行匹配重复所有基表行 [英] Repeat all base table rows for EACH left join row match
问题描述
我在简洁地描述我所需要的内容时遇到了麻烦，因此，感谢您对标题的任何帮助！
I am having trouble succinctly describing what I need, so any help editing the title is appreciated!
我有3张桌子:
-
Jobs(具有JobIDPK) -
JobsDetail(具有JobIDFK和DepartmentsIDFK) -
Departments(具有DepartmentsIDPK)
Jobs(hasJobIDPK)JobsDetail(hasJobIDFK andDepartmentsIDFK)Departments(hasDepartmentsIDPK)
Departments的记录数较少(〜10)
Departments will have a low number of records (~10)
如果我有10个Department行，那么我需要为每个Jobs记录重复所有10行.不在作业的JobsDetail记录中的DepartmentID将在作业"列中显示NULL值.对于一个Jobs记录，典型的LEFT JOIN可以按照我想要的方式工作:
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
产生以下结果:
Department JobIdentifier
310 NULL
320 NULL
430 NULL
450 NULL
460 NULL
500 NULL
530 1000
533 1000
534 1000
535 NULL
当然，当添加另一个Jobs记录时，我会看到:
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
这怎么完成?
这是表中的一些测试数据:
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
推荐答案
您应该从工作和部门之间的交叉联接开始，然后从那里建立查询:
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
我对JobId和JobIdentifier之间的区别还不清楚，所以可能不太正确.
I'm a bit unclear on the distinction between JobId and JobIdentifier, so this may not be quite right.
如果将ji.JobIdentifier添加到select列表中，即使没有匹配项，您也会看到部门 应该属于哪个工作.
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.
这篇关于对每个左连接行匹配重复所有基表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
