如果fieldb为null，如何仅返回基于fielda的不同行？ [英] How to return only distinct lines based on fielda if fieldb is null?

问题描述

我有两张桌子我正试图加入：

 SELECT Name FROM List3，（SELECT List1.Name FROM List1 LEFT JOIN List2 ON List1.Name = List2.Name）AS Joined WHERE Joined.Rate = List3.Rate AND Joined.Size = List3.Size 

从我的测试用例中，这似乎正如我想要的那样工作。但是，我现在正试图让它允许Joined可能以无效率结束的情况（如果可能的话甚至是无法匹配的结果）。



我尝试过：

 WHERE（Joined.Rate = List3.Rate AND Joined.Size = List3.Size）或者Joined.Rate IS NULL 

由于其他字段的数量，结果为我提供了几百个匹配的单个项目NULL率。



我想要的只是每次只显示一个名为Joined.Name的NULL。

解决方案

< blockquote>我建​​议你阅读： SQL连接的可视化表示 [ ^ ]。这可能有助于理解 LEFT JOIN 的作用。



此外，很难说你有什么改进以返回单个项目而不会看到您的数据...



有关详细信息，请参阅：访问子查询高级技术 [ ^ ]

I have two tables I'm trying to join together:

SELECT Name FROM List3, (SELECT List1.Name FROM List1 LEFT JOIN List2 ON List1.Name = List2.Name) AS Joined WHERE Joined.Rate = List3.Rate AND Joined.Size = List3.Size

From my test cases, this seems to be working exactly as I want. However, I'm now trying to make it allow situations where Joined might end up with a null Rate (or an unmatchable result if that's even possible).

What I have tried:

WHERE (Joined.Rate = List3.Rate AND Joined.Size = List3.Size) OR Joined.Rate IS NULL

Because of the number of other fields, the result gives me a couple hundred matches for the single item with NULL Rate.

What I want is to only show each NULL rated Joined.Name only once.

解决方案

I'd suggest to read this: Visual Representation of SQL Joins[^]. This might be helpful in understanding what LEFT JOIN does.

Besides, it's hard to say what you have to improve to return single item without seeing your data...

For further information, please see: Access Subquery Advanced Techniques[^]

这篇关于如果fieldb为null，如何仅返回基于fielda的不同行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！