Cardinality violation when using a subquery that r

问题:

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);

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

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

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.

I'm using Mysql

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

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

回答1:

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)

or you can JOIN with the same table twice by giving the joined tables an alias.

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..