有没有简单的方法在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 ... 30There 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 nullThe 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_ID2If 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)最满意答案
可能这一点,取决于你的数据的内容UNION ALL将保留所有数据,UNION将删除重复
SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID1 UNION ALL SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID2Possibly this, depend on the content of your data UNION ALL will keep all data, UNION will remove duplicate
SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID1 UNION ALL SELECT [fieldList] FROM lt a INNER JOIN st b ON a.Log_ID = b.Log_ID2更多推荐
发布评论