英文:
How to associate three tables？
问题
我有三个表ABC。我希望在连接BC的同时显示A的所有记录。A的字段a1与B的字段b1相关联。a1可能为空，所以我写了：
A.a1 = B.b1(+)
表C的两个字段c和d分别与A.a2和B.b2相关联，所以我写了：
A.a2 = C.c(+) and B.b2 = C.d(+)
完整的SQL如下：
select A.a1, A.a2, B.e, C.f
from A, B, C
where A.a1 = B.b1(+)
and A.a2 = C.c(+)
and B.b2 = C.d(+)
但提示说一个表最多只能有一个外部连接。
我尝试使用case when来显示B和C的信息，如下：
select A.a1, A.a2,
case when a1 is null then null
else (select B.e from B
where B.b1=A.a1) end,
case when a1 is null then null
when (select B.e from B
where B.b1=A.a1) is null then null
else (select C.f from C
where C.c=A.a2 and C.d=B.b2) end
from A
但是否有其他更好的关联方法？
英文:
I have three tables ABC. I hope to display all records of A while linking BC. Field a1 of A is associated with field b1 of B. a1 may be empty, so I wrote
A.a1=B.b1(+)
The two fields c and d of table C are associated with A.a2 and B.b2 respectively
so i wrote
A.a2 = C.c(+) and B.b2 = C.d(+)
The total sql is as follows
select A.a1, A.a2, B.e,C.f,
from A, B, C
where A.a1=B.b1(+)
and A.a2 = C.c(+)
and B.b2 = C.d(+)
But the prompt says that a table can only have one external link at most.
I tried to use case when to display the information of B and C,
select A.a1, A.a2,
case when a1 is null then null
else (selece B.e from B
where B.b1=A.a1) end,
case when a1 is null then null
when (selece B.e from B
where B.b1=A.a1) is null then null
else (selece C.f from C
where C.c=A.a2 and C.d=B.b2) end
from A
but is there any other better association method?
答案1
得分: 0
"Older" (I believe lower than 21c) Oracle database versions won't let you outer join one table to two or more other tables using the "old" Oracle's (+) outer join operator.
But, if you switch to JOINs, then you won't have that problem. Something like this:
select *
from b left join a on a.a1 = b.b1
left join c on c.d = b.b2 and c.c = a.a2
(Fetch columns you want, include conditions you need, but - that's the general idea.)
英文:
"Older" (I believe lower than 21c) Oracle database versions won't let you outer join one table to two or more other tables using the "old" Oracle's (+) outer join operator.
But, if you switch to JOINs, then you won't have that problem. Something like this:
select *
from b left join a on a.a1 = b.b1
left join c on c.d = b.b2 and c.c = a.a2
(Fetch columns you want, include conditions you need, but - that's the general idea.)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论