使用返回两个值的子查询时违反基数 [英] Cardinality violation when using a subquery that returns two values

问题描述

我已经创建了一个素描这样的sql查询

I have create a sql query that the sketch is like this

select *
from   A
where  A.id in (select  B.id1, B.id2 from B);

其中，主选择返回那些A.id与B.id1或B一致的值。 id2。

where the main select returns those values for which A.id coincides with either B.id1 or B.id2.

很显然，此解决方案不起作用，因为where子句中的基数不匹配。我该如何克服这个问题？

Clearly this solution doesn't work as the cardinality doesn't match in the where clause. How can I overcome this problem?

一种解决方案是进行两个子查询，一个用于B.id1，另一个用于B.id2，但作为我的子查询，查询比在此示例中要长得多。

One solution would be to make two sub-queries, one for B.id1 and one for B.id2, but as my sub-query is much longer than in this example I was looking for a more elegant solution.

我正在使用Mysql

I'm using Mysql

编辑1
只要语法比使用两个子查询简单，使用联接我就没有问题

EDIT 1 As long as the syntax is simpler than using two sub-queries I have no issues using joins

编辑2
感谢@NullSoulException。我尝试了第一个解决方案，并按预期工作！！

EDIT 2 Thanks @NullSoulException. I tried the first solution and works as expected!!

推荐答案

类似下面的方法可以解决问题。

Something like the below should do the trick.

select * 
From table1 a , (select id1 , id2 from table2 ) b 
where (a.id = b.id1) or  (a.id = b.id2)

或者您可以加入

select * from table1 a 
INNER JOIN table2 b1 on a.id = b1.id1
INNER JOIN table2 b2 on a.id = b2.id2

请针对您的数据集/表测试以上内容。

Please test the above against your datasets/tables..

这篇关于使用返回两个值的子查询时违反基数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！