一个查询!

[phyedu]

a表:
yhid(主)
1
2
3
b表:
yhid,s,d
1 s1,s2
1 s2,s3
2 s1,s2
c表:
yhid,s,d,
1 d1,d2
a表与b,c表都是1对多的关系,b表有的yhid,c表不一定有,同样,c表有的yhid,b表不一定有
给a表一个查询条件,希望得到
yhid,s,d
1 s1,s2
1 s2,s3
1 d1,d2

[zhaojianmi1]

先坐下

[zhaojianmi1]

select * from b where yhid in ((select yhid from b) intersect (select yhid from c))
union
select * from c where yhid in ((select yhid from b) intersect (select yhid from c))

[999朵玫瑰]

学习下

[phyedu]

有错误 intersect?

[phyedu]

intersect是Analysis Services，而不是Transact-SQL，不能作为SQL语句使用??

[DEATH64]

表B和表C同时有,取那个?

[xiaoxiangqing]

select * from b where yhid in(select yhid from b union select yhid from c)
union
select * from c where yhid in(select yhid from b union select yhid from c)

[Oraclers]

select b.yhid,b.s,b.d from a join b on a.yhid=b.yhid where a.yhid=1
union
select c.yhid,c.s,c.d from a join c on a.yhid=c.yhid where a.yhid=1

[Oraclers]

或者：
select *
from (select b.* from b union select c.* from c) q join a on a.yhid=q.yhid
where a.yhid=1

[Oraclers]

[Quote=引用 9 楼 oraclers 的回复:]
或者：
select *
from (select b.* from b union select c.* from c) q join a on a.yhid=q.yhid
where a.yhid=1
[/Quote]
select q.*
from (select b.* from b union select c.* from c) q join a on a.yhid=q.yhid
where a.yhid=1