使用 LEFT JOIN 的 SELECT 语句 [英] SELECT statement using LEFT JOIN
问题描述
我有一张在 Access 中定期更新的表格.
I've got a table that is periodically updated in Access.
这个表很简单，由2列组成.我将用 2 个标识符表示它们:列 Operadora 和列 NDC.一个Operadora可能有N个NDC.
This table is very simple, composed of 2 Columns. I will represent them with 2 identifiers: Column Operadora and column NDC. One Operadora may have N NDC.
该表是永久性的，每天都会对其进行 2 次操作:对所有列执行 DELETE 操作和对来自 .txt(每天更新)的链接表执行的 APPEND 操作.
This table is permanent, and 2 operations are done every single day to it: A DELETE to all columns and an APPEND, done from a linked table that comes from a .txt (that is updated every day).
我需要在附加之前捕获存在的表之间的差异:存在于其中但不在链接表的新"版本中的值，以及其中不存在但在链接表的新"版本中的值.
I need to capture differences between the table that exists before it is appended: The values that are present in it but are not in the "new" version of the linked table, AND The values that are not present in it but are in the "new" version of the linked table.
我已经使用一些示例设法通过 LEFT JOIN 找到了一种方法，但确实不是我的表.我的表有许多要比较的值(比如 1000)，它只返回上面 SELECT 的列名，即使同一个选择返回我想要的其他示例的值.
I've managed to find a way of doing this with a LEFT JOIN using some examples, but not with my tables indeed. My table has many values (something like 1000) to be compared, and it is returning only the column names for the SELECT above, even though the same select returns the values i want with another examples.
这是我的 SELECT 示例，用于尝试查找问题的第一部分(存在于表中但不在链接表中的值)，因此任何人都可以帮助我找到问题:
Here is my SELECT sample to try finding the first part of the problem (values that are present in the table but not in the linked table), so anyone can help me finding the problem:
SELECT [1A - NDC_MSISDN].Operadora, [1A - NDC_MSISDN].NDC
FROM [1A - NDC_MSISDN] LEFT JOIN [Linked NDC MSISDN TXT] ON [1A - NDC_MSISDN].[Operadora] = [Linked NDC MSISDN TXT].[Operadora]
WHERE ((([Linked NDC MSISDN TXT].Operadora) Is Null));
附注:
1A - NDC_MSISDN -> 要比较的固定表(值为999999")Linked NDC MSISDN TXT -> 要比较的链接表(没有值999999")
1A - NDC_MSISDN -> Fixed table to be compared (with values "999999")
Linked NDC MSISDN TXT -> Linked table to be compared (without values "999999")
链接的 NDC MSISDN TXT:
Linked NDC MSISDN TXT:
Operadora;NDC_MSISDN
A;491
A;492
A;493
A;494
A;495
A;496
A;497
A;498
A;499
A;490
A;770005
B;71
C;82
D;50
E;980
F;385
G;303
G;308
G;402
G;620
G;719
G;785
G;758
G;970
H;9
H;70100
H;70101
H;70102
H;70103
H;801
H;802
H;80769
H;8077
H;8078
H;80790
H;80791
H;80792
H;808
H;8092
H;8095
H;8099
H;9010
H;90111
H;90112
H;9014
H;9015
H;9016
H;90187
H;90188
H;90189
H;90198
H;90199
H;9021
H;9022
H;9023
H;9024
H;9025
H;9026
H;9027
H;9030
H;9031
H;9032
H;9033
H;90340
H;90346
H;90347
H;90348
H;90349
H;9040
H;90411
H;90412
H;90413
H;90414
H;9043
H;9045
H;9046
H;9047
H;9048
H;9049
H;905
H;90669
H;90676
H;90677
H;90678
H;90679
H;90680
H;90681
H;90682
H;90684
H;90688
H;90689
H;9069
H;9070
H;9071
H;9072
H;9073
H;9074
H;9076
H;9077
H;9078
H;9079
H;90800
H;90801
H;90802
H;90803
H;90841
H;90842
H;90857
H;90858
H;90859
H;90863
H;90864
H;90865
H;90866
H;90867
H;90868
H;90869
H;9087
H;9088
H;9089
H;9090
H;9095
I;400
I;401
I;404
I;406
I;408
I;409
I;410
I;411
I;414
I;415
I;416
I;417
I;418
I;419
I;453
I;456
I;457
I;458
I;459
I;461
I;462
I;465
I;466
I;468
I;469
I;470
I;471
I;474
I;475
I;476
I;477
I;478
I;479
I;480
I;481
I;482
I;483
I;484
I;485
I;488
I;489
I;580000
I;5800020
I;58000210
I;5800030
I;5800041
I;5800042
I;5800050
I;5800076
I;580010
I;590
I;591
I;592
I;595
I;900
I;901
I;902
I;903
I;904
I;905
I;906
I;907
I;908
I;909
I;910
I;911
I;912
I;913
I;914
I;915
I;916
I;917
I;918
I;919
I;921
I;923
I;925
I;927
I;929
I;931
I;933
I;935
I;937
I;939
I;940
I;941
I;943
I;944
I;945
I;947
I;948
I;949
I;950
I;951
I;952
I;953
I;954
I;955
I;956
I;957
I;958
I;959
I;960
I;961
I;962
I;963
I;964
I;965
I;966
I;967
I;968
I;969
I;970
I;971
I;972
I;973
I;974
I;975
I;976
I;977
I;979
I;983
I;985
I;987
I;989
I;990
I;991
I;992
I;993
I;994
I;995
1A - NDC_MSISDN(以999999"添加到每个operadora来举例说明):
1A - NDC_MSISDN (With "999999" added to each operadora to exemplify):
Operadora;NDC_MSISDN
A;491
A;492
A;493
A;494
A;495
A;496
A;497
A;498
A;499
A;490
A;770005
A;9999999
B;71
B;9999999
C;82
C;9999999
D;50
D;9999999
E;980
E;9999999
F;385
F;9999999
G;303
G;308
G;402
G;620
G;719
G;785
G;758
G;970
G;999999
H;9
H;70100
H;70101
H;70102
H;70103
H;801
H;802
H;80769
H;8077
H;8078
H;80790
H;80791
H;80792
H;808
H;8092
H;8095
H;8099
H;9010
H;90111
H;90112
H;9014
H;9015
H;9016
H;90187
H;90188
H;90189
H;90198
H;90199
H;9021
H;9022
H;9023
H;9024
H;9025
H;9026
H;9027
H;9030
H;9031
H;9032
H;9033
H;90340
H;90346
H;90347
H;90348
H;90349
H;9040
H;90411
H;90412
H;90413
H;90414
H;9043
H;9045
H;9046
H;9047
H;9048
H;9049
H;905
H;90669
H;90676
H;90677
H;90678
H;90679
H;90680
H;90681
H;90682
H;90684
H;90688
H;90689
H;9069
H;9070
H;9071
H;9072
H;9073
H;9074
H;9076
H;9077
H;9078
H;9079
H;90800
H;90801
H;90802
H;90803
H;90841
H;90842
H;90857
H;90858
H;90859
H;90863
H;90864
H;90865
H;90866
H;90867
H;90868
H;90869
H;9087
H;9088
H;9089
H;9090
H;9095
H;9999999
I;400
I;401
I;404
I;406
I;408
I;409
I;410
I;411
I;414
I;415
I;416
I;417
I;418
I;419
I;453
I;456
I;457
I;458
I;459
I;461
I;462
I;465
I;466
I;468
I;469
I;470
I;471
I;474
I;475
I;476
I;477
I;478
I;479
I;480
I;481
I;482
I;483
I;484
I;485
I;488
I;489
I;580000
I;5800020
I;58000210
I;5800030
I;5800041
I;5800042
I;5800050
I;5800076
I;580010
I;590
I;591
I;592
I;595
I;900
I;901
I;902
I;903
I;904
I;905
I;906
I;907
I;908
I;909
I;910
I;911
I;912
I;913
I;914
I;915
I;916
I;917
I;918
I;919
I;921
I;923
I;925
I;927
I;929
I;931
I;933
I;935
I;937
I;939
I;940
I;941
I;943
I;944
I;945
I;947
I;948
I;949
I;950
I;951
I;952
I;953
I;954
I;955
I;956
I;957
I;958
I;959
I;960
I;961
I;962
I;963
I;964
I;965
I;966
I;967
I;968
I;969
I;970
I;971
I;972
I;973
I;974
I;975
I;976
I;977
I;979
I;983
I;985
I;987
I;989
I;990
I;991
I;992
I;993
I;994
I;995
I;999999
我想要的输出是:
A;999999
B;999999
C;999999
D;999999
E;999999
F;999999
G;999999
H;999999
I;999999
推荐答案
我的猜测是您只加入一列，但要寻找两列的可变性.因此，尝试更新您的 join 子句:
My guess is that you're only joining on one column, but looking for variability in both columns. So, try updating your join clause:
SELECT [1A - NDC_MSISDN].Operadora, [1A - NDC_MSISDN].NDC
FROM [1A - NDC_MSISDN]
LEFT JOIN [Linked NDC MSISDN TXT]
ON [1A - NDC_MSISDN].[Operadora] = [Linked NDC MSISDN TXT].[Operadora]
AND [1A - NDC_MSISDN].[NDC] = [Linked NDC MSISDN TXT].[NDC]
WHERE (([Linked NDC MSISDN TXT].Operadora) Is Null);
这篇关于使用 LEFT JOIN 的 SELECT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
