我在oracle中创建一个查询似乎不想加入缺少值的问题
我有这个表：
table myTable(refnum,contid,type) values are: 1,10,90000 2,20,90000 3,30,90000 4,10000 5,10000 6,20000 7,20000 8,20000
这是我以后的领域的分解：
select a.refnum from myTable a where type = 90000 select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000) select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)
我以后查询的结果是：
a.refnum,b.refnum,c.refnum
我以为这会工作：
select a.refnum,c.refnum from myTable a left outer join myTable b on (a.contid = b.contid) left outer join myTable c on (a.contid = c.contid) where a.id_tp_cd = 90000 and b.id_tp_cd = 10000 and c.id_tp_cd = 20000
所以值应该是：
1,null,6 2,4,7 3,5,8
但它唯一的回报：
2,8
我以为左连接会显示左边的所有值,并为右边创建一个空值.
帮帮我 ：(
你说正确的是,左连接将返回没有匹配的权利的null,但是当你将这个限制添加到你的where子句时,你不允许返回这些null：
and b.id_tp_cd = 10000 and c.id_tp_cd = 20000
您应该可以将它们放在连接的“on”子句中,因此只返回右侧的相关行.
select a.refnum,c.refnum from myTable a left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000) left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000) where a.id_tp_cd = 90000
