如何从两个不同的表中选择不同的值

I have two tables like table1:

我有两个表,如table1:

id   name   posId   Mid
1    sam    1       10
2    sid    1       10
3    jeet   1       10

table2:

id   name   posid   Mid
1    Anin   2       10
2    Nir    2       10
3    jeev   2       10

I want to have a table like...

我想要一张像...的桌子

posid
1
2

ie; i want to have distinct "posid" by joining table1 and table2 where "Mid" will be same for table1 and table2

即;我希望通过连接table1和table2来获得不同的“posid”,其中“Mid”对于table1和table2是相同的

3 个解决方案

#1

You can do something like this:

你可以这样做:

select distinct t1.posId
from t1
where not exists (select 1 from t2 where t2.posId = t1.posId)
union all
select distinct t2.posId
from t2
where not exists (select 1 from t1 where t2.posId = t1.posId);

I think I misinterpeted the question. You can use join:

我想我错了这个问题。你可以使用join:

select t1.posid, t2.posid
from t1 join
     t2
     on t1.mid = t2.mid;

To get this as a column, you need to unpivot. Here is one method:

要将此作为列,您需要取消删除。这是一种方法:

select distinct (case when n.which = 1 then t1.posid else t2.posid end) as posid
from t1 join
     t2
     on t1.mid = t2.mid cross join
     (select 1 as which union all select 2 as which) n

#2

You can use join with union

您可以使用join with union

select t1.posid
from t1 join t2 on t1.mid = t2.mid
union
select t2.posid
from t1 join t2 on t1.mid = t2.mid

#3

I understand you want distinct posid from both tables.

我知道你想从两个表中获得明显的posid。

This isnt efficient but works:

这不是有效的,但有效:

select distinct aa.posid
from
    (select distinct table1.posid as posid
       from table1
       join table2
         on table1.mid=table2.mid
     UNION ALL
     select distinct table2.posid as posid
       from table1
       join table2
         on table1.mid=table2.mid) aa

#1