这是我经常遇到的SQL问题!(我使用Sql-Server / sql-management studio)我想要左连接一张表,但只有在它满足需要另一个连接的测试时才能连接。所以我将B加入到A中,但只有在B连接到表C的连接通过测试时,才想将B连接到A中...
SELECT A.*
FROM TableA A
LEFT JOIN TableB B on A.BID = B.BID --only want to join B if C passes the test
LEFT JOIN TableC C on B.CID = C.CID
WHERE C.PassesTest -- not correct: throws out records from A that don't pass test
所以,如果C通过测试,我只想左连接B。就像我的标题一样,我基本上想要左连接2个内部连接的表...“A left join(b inner join c)on C.PassesTest”就是我想做的事情。
我的两个立即想到的方法都失败了:
1. 如果你把PassesTest放在和C连接的地方(...left join c on B.CID=C.CID AND C.PassesTest...),你仍然会保留不需要的所有B记录。
2. 如果你把测试放在where子句中(像上面那样),它会拒绝所有未通过测试的A记录,但我想保留这些记录(因为这是一个左连接)。
我能想到两个解决方案,但它们都有点麻烦...难道没有更简单的方法吗?
join on all of B/C - this is logically what I want but obviously not optimal as it is grabbing EVERYTHING from B and C before the join (so it's slow)
SELECT A.* FROM TableA A LEFT JOIN ( SELECT * FROM TableB B INNER JOIN TableC C ON B.CID = C.CID WHERE C.PassesTest ) BC ON BC.BID = A.BID
Do some kind of nested select. The way below seems a little redundant... Maybe it can be written a little better
SELECT ABC.StuffFromA FROM ( SELECT * FROM TableA A LEFT JOIN TableB B on A.BID = B.BID LEFT JOIN TableC C ON B.CID = C.CID ) ABC LEFT JOIN TableB B on ABC.BID = B.BID AND ABC.PassesTest
无论如何,这是我经常遇到的问题,我总是发现自己不得不做出非常困难的事情才能让它正常工作!似乎应该有更简单的解决方案……或者这只是一个表面上看起来简单实则很棘手的 SQL 问题?