在SQL Server中的单个JOIN语句中查找0,1个或可能的2个匹配项(Find 0, 1, or possibly 2 matches within a single JOIN statemen

编程入门行业动态更新时间:2024-10-28 06:23:38

在SQL Server中的单个JOIN语句中查找0,1个或可能的2个匹配项(Find 0, 1, or possibly 2 matches within a single JOIN statement in SQL Server)

有没有简单的方法在JOIN语句中查找来自同一个表行的两个不同匹配项？例如，我有一个名为st的存储表，如下所示：

Unique ID Log_ID1 [other fields] Log_ID2 1 25 ... 30

有一个名为lt的日志表，它在Log_IDx字段被填充时写入，它看起来像这样：

Log_ID SomeDetail 1 ... ... ... 25 I need this part if Log_ID1 is not null ... ... 30 I also need to get this part if Log_ID2 is not null

表st将用一些数据初始化，然后根据需要用Log_ID1和Log_ID2数据更新该行。如果任何一个存在，我需要获取日志数据，或者如果两者都存在，我需要找到两行。我试过这个，它不起作用：

SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID1 INNER JOIN st c ON a.Log_ID = c.Log_ID2

如果我删除了INNER JOIN，它可以工作，但是如果填充了Log_ID1和Log_ID2，它并不会返回所有数据（显然）。出于这个原因，这也不起作用：

SELECT [fieldList] FROM lt a INNER JOIN (st b ON a.Log_ID = b.Log_ID1) [AND][OR] (st c ON a.Log_ID = b.Log_ID2)

可以在两个Log_ID字段中都有NULL，或者两个都包含数据。

是否有一个简单/优雅的结构，可以根据存储表的状态抽取0,1或2行数据？或者我将不得不编写一些难看的代码来获取我所追求的内容？也许是某种联盟？

从语法上来说，因为它可能更容易理解，所以我正在寻找可以做到这一点的东西，但是是合法的语法：

SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID IN (b.Log_ID1, b.Log_ID2)

Is there a simple way to find potentially two different matches from the same table row in a JOIN statement? For example, I have a storage table called st that looks like this:

Unique ID Log_ID1 [other fields] Log_ID2 1 25 ... 30

There is a log table called lt that is written to when the Log_IDx fields get filled in and it looks like this:

Log_ID SomeDetail 1 ... ... ... 25 I need this part if Log_ID1 is not null ... ... 30 I also need to get this part if Log_ID2 is not null

The table st will get initialized with some data and then, as necessary, the row will be updated with the Log_ID1 and Log_ID2 data. I need to get to the log data if either one exists, or if both exist, I need to get to both rows. I've tried this and it doesn't work:

SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID1 INNER JOIN st c ON a.Log_ID = c.Log_ID2

If I remove either INNER JOIN, it works, but it doesn't return all the data (obviously) if both Log_ID1 and Log_ID2 are populated. For that reason, this also doesn't work:

SELECT [fieldList] FROM lt a INNER JOIN (st b ON a.Log_ID = b.Log_ID1) [AND][OR] (st c ON a.Log_ID = b.Log_ID2)

It's possible to have NULLs in both Log_ID fields, in either one, or to have both of them contain data.

Is there a simple/elegant construct that can pull 0, 1 or 2 rows of data depending on the state of the storage table? Or will I have to write some ugly code to get what I'm after? Maybe a union of some sort?

Syntactically speaking, since it may be easier to understand, I'm looking for something that would do this, but be legal syntax:

SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID IN (b.Log_ID1, b.Log_ID2)