现在学生表str,字段sid,sname,tid;
老师表tec,字段tid,tname;我要通过学生的id,找到sname,tname,*有些学生没有老师,但也要能查出sname, tname显示为空字符串
求各位大神帮助,谢谢
老师表tec,字段tid,tname;我要通过学生的id,找到sname,tname,*有些学生没有老师,但也要能查出sname, tname显示为空字符串
求各位大神帮助,谢谢
调试欢乐多
FROM [str] A
LEFT JOIN tec B ON A.tid = B.tid
SELECT A.sname,ISNULL(B.tname) as tname
FROM stu A
LEFT JOIN tec B ON A.tid = B.tid and a.stuid=0
结果是sname tnme
张三 1
李四 1结果很奇怪了,再请指教
2 李四 1tec表 1 王五
use Test
go
create table tec
(
tid int,
tname varchar(20)
)
go
create table str
(
sid int,
sname varchar(20),
tid int
)
go
insert into tec
select 001,'teacher1' union all
select 002,'teacher2' union all
select 003,'teacher3' insert into str
select 1,'student1',001 union all
select 2,'student2',002 union all
select 3,'student3',003 union all
select 4,'student4',004 select * from tec select isnull(tec.tname,'')as tname,
str.sname
from tec
right join str on tec.tid=str.tid ---------------------------------------------------
tname sname
-------------------- --------------------
teacher1 student1
teacher2 student2
teacher3 student3
student4(4 行受影响)
select str.sname,isnull(tec.tname,'')
from str
left join tec on str.tid=tec.tid
where id=?
from str
left join tec on str.tid=tec.tid
where str.sid=?