创建复杂的查询

[jy02945722]

现在有3张表，A表有字段AA、DD，B表有字段BB、XX，C表有字段CC、YY，A表字段DD有可能是B表的BB或者C表的CC，要做一个查询包括A表的AA、DD，第三个字段根据DD显示XX或者YY，请问要怎么查呢？

[mengmou]

select a.*.b.xx as xy
from a
join b on b.bb = a.dd
union all
select a.*,c.yy as xy
from a
join c on c.cc = a.dd

[子陌红尘]

select A.*,B.XX from A,B where A.DD=B.BB
union all
select A.*,C.YY from A,C where A.DD=C.CC

[jy02945722]

谢谢回帖，我试试

[paoluo]

感覺是這樣

Select
A.AA,
A.DD,
IsNull(B.XX, C.YY) As XXYY
From
A
Left Join
B
On A.DD = B.BB
Left Join
C
On A.DD = C.CC

[dawugui]

现在有3张表，A表有字段AA、DD，B表有字段BB、XX，C表有字段CC、YY，A表字段DD有可能是B表的BB或者C表的CC，要做一个查询包括A表的AA、DD，第三个字段根据DD显示XX或者YY，请问要怎么查呢？

select a.aa,b.xx dd from a,b where a.dd = b.bb
union all
select a.aa,c.yy dd from a,c where a.dd = b.cc

[fa_ge]

select a.aa,b.xx dd from a,b where a.dd = b.bb
union all
select a.aa,c.yy dd from a,c where a.dd = c.cc

dawugui(潇洒老乌龟),打錯了吧

[jy02945722]

太感谢了
结帖

[jy02945722]

但是是用where a.dd=b.bb还是from a join b on a.dd=b.bb效率高呢？？？？

[fa_ge]

from a join b on a.dd=b.bb效率高

[paoluo]

jy02945722() ( ) 信誉：100 2007-08-13 11:47:39 得分: 0


但是是用where a.dd=b.bb还是from a join b on a.dd=b.bb效率高呢？？？？

---------
一樣的

[paoluo]

用union和用Left Join都可以實現

select A.*,B.XX from A,B where A.DD=B.BB
union all
select A.*,C.YY from A,C where A.DD=C.CC



Select
A.AA,
A.DD,
IsNull(B.XX, C.YY) As XXYY
From
A
Left Join
B
On A.DD = B.BB
Left Join
C
On A.DD = C.CC

[jy02945722]

再次感谢各位！！！