sql - SQL右连接返回空白行
问题描述
我正在尝试加入下表:
表 A:
Type1 Type2 ID Object Location
Sample Dummy 1 X111 111222
Sample Dummy 2 X333 333444
Sample Dummy 2 X333 555666
Sample Red 1 X222 666777
Test Red 1 X222 666777
Test Red 1 X222 666777
表 B:
Source Type2 ID Object Logged
SR1 Dummy 1 X111 17
SR1 Dummy 2 X333 1
SR1 Red 1 X222 12
SR2 Dummy 1 X111 9
SR2 Dummy 2 X333 9
SR2 Red 1 X222 20
我的 SQL 代码:
SELECT B.Source, B.Type2, B.ID, B.Object, B.Logged, A.Location
FROM A RIGHT JOIN B ON
A.Type1='Sample' AND
A.Type2=B.Type2 AND
A.ID=B.ID AND
A.Object=B.Object
WHERE B.Source='SR2'
我期待以下结果:
Source Type2 ID Object Logged Location
SR1 Dummy 1 X111 17 111222
SR1 Dummy 2 X333 1 333444
SR1 Dummy 2 X333 1 555666
SR1 Red 1 X222 12 666777
但我得到了这个:
Source Type2 ID Object Logged Location
SR1 Dummy 1 X111 17 111222
SR1 Dummy 2 X333 1 -
SR1 Dummy 2 X333 1 -
SR1 Red 1 X222 12 666777
我的代码有什么问题?请帮忙。
解决方案
LEFT JOIN
更容易遵循。问题是过滤b.Type1 = 'Sample'
。据我所知,这是不需要的:
SELECT B.Source, B.Type2, B.ID, B.Object, B.Logged, A.Location
FROM B LEFT JOIN
A
ON A.Type2 = B.Type2 AND
A.ID = B.ID AND
A.Object = B.Object
WHERE B.Source = 'SR2';