高分求多表查询语句

[kaka009]

table1
create table vits
(
Vits_ID int identity(1,1) PRIMARY KEY,
Vits_name varchar(20) not null
)
go
table 2
create table specialties
(
Specialties_ID int identity(1,1) primary key,
Specialties_name varchar(20) not null
)
go
table 3
create table vet_specialties ---此表可不要
(
Vit_ID int foreign key REFERENCES vets(Vits_ID) ,
Specialty_ID int foreign key REFERENCES specialties(Specialties)
)
go


这三张表是查询兽医名和他的专科
要求 一位兽医可有多个专科
一个专科可有多个兽医
查询一位兽医 此兽医名和对应其所有专业都查询出来

[robert_ypeng]

select vits.Vits_name,specialties.Specialties_name
from vits
full outer join vet_specialties
on vits.Vits_ID = vet_specialties.Vits_ID
full outer join specialties
on specialties.Specialty_ID = vet_specialties.Specialty_ID
where vits.Vits_name = ??

[paoluo]

Select A.Vits_name,C.Specialties_name
From vits A
Inner Join vet_specialties B
On A.Vits_ID=B.Vit_ID
Inner Join specialties C
On B.Specialty_ID=C.Specialties_ID



這樣不可以嗎？？

[paoluo]

加上查詢條件的話

Select A.Vits_name,C.Specialties_name
From vits A
Inner Join vet_specialties B
On A.Vits_ID=B.Vit_ID
Inner Join specialties C
On B.Specialty_ID=C.Specialties_ID
Where A.Vits_ID=條件

或者

Select A.Vits_name,C.Specialties_name
From vits A
Inner Join vet_specialties B
On A.Vits_ID=B.Vit_ID
Inner Join specialties C
On B.Specialty_ID=C.Specialties_ID
Where A.Vits_name=條件

[kaka009]

谢谢 我试试

[CSDMN]

Select A.Vits_name,C.Specialties_name
From vits A
left Join vet_specialties B
On A.Vits_ID=B.Vit_ID
left Join specialties C
On B.Specialty_ID=C.Specialties_ID
Where A.Vits_name=條件


用left join可以查出没有专业的兽医

[kaka009]

不对啊

[kaka009]

这个vet_specialties_ID 是什么?

[paoluo]

kaka009(卡卡007) ( ) 信誉：100 2006-08-09 10:28:00 得分: 0


这个vet_specialties_ID 是什么?



---------------------
這個，我還要問你呢。

我的語句裡沒有這麼個東西啊。

[kaka009]

呵呵 Inner Join vet_specialties B.....
这个vet_specialties
是什么?
我现在还是个学生 还很嫩 呵呵 谢谢

[paoluo]

我暈，vet_specialties 不是你自己的表名嗎？？後面的B是別名。