Question

I have a 2 tables with a many-2-many relationship:
tableA (ida, valA)
tableB (idb, valB)
tableAB (ida, idb)

I need one query that should return in every tupple:
(ida, idb, 0/1 if related or not)

Here is some input / output example: tableA

ida   valA
1     b
2     s
3     l

<强 > 表格B

idb   valB
11    aaa
22    bbb
33    ccc

<强度表AB

ida   idb
1     11
2     33

预期成果:

ida      idb      is_exists
1        11       1
2        11       0
1        33       0
2        33       1

Note that I don t need permutations which are always 0, for example ida=3 or idb=22
this is because it means that in a matrix the whole row or column is 0 (this shows that the row has no relation whatsoever with the other table)

    ida |     |     |  
idb     |  1  |  2  |  3
---------------------------
11       |  1  |  0  |  0
---------------------------
22       |  0  |  0  |  0
---------------------------
33       |  0  |  1  |  0

Answer 1

您需要的是一张有各种可能配对的驾驶表。一种方法是从表A和表B获得这个, 使用交叉连接 :

select allAB.aid, allAB.bid, max(case when ab.aid is not null then 1 else 0 end) as HasPair
from (select distinct a.id as aid, b.id as bid
      from TableA a cross join
           TableB b
     ) as allAB left outer join
     TableAB ab
     on allAB.aid = ab.aid and
        allAB.bid = ab.bid
group by allAB.aid, allAB.bid

在此之后,查询只是总结和确定表AB中是否有相应的记录。

友情链接