我现在有这样的表,如:
表一:T1
T1id name
1 aa
2 bb
3 cc
表二:T2
T2id active
1 1
2 2
T1id和T2id是相关联的,现在我想查询name及其相关的active,如果该name没有active,则显示为空,即如下结果:
----------------
name active
aa 1
bb 2
cc
这样的语句该怎么写呢?谢谢!
表一:T1
T1id name
1 aa
2 bb
3 cc
表二:T2
T2id active
1 1
2 2
T1id和T2id是相关联的,现在我想查询name及其相关的active,如果该name没有active,则显示为空,即如下结果:
----------------
name active
aa 1
bb 2
cc
这样的语句该怎么写呢?谢谢!
T1.name,isnull(T2.[active],'') as active
from
T1
left join
T2
on
T1.T1id=T2.T2id
select T1.name, T2.active
from T1 left join T2 on T1.T1id = T2.T2id
比如说还有第三个表
T3
T3id activName
1 乒乓球
2 羽毛球
现在要这样的结果
----------------
name active
aa 乒乓球
bb 羽毛球
cc
insert into t1 values(1,'aa')
insert into t1 values(2,'bb')
insert into t1 values(3,'cc')
create table t2 (T2id int,active varchar(10))
insert into t2 values(1,'1' )
insert into t2 values(2,'2' )
goselect a.name , isnull(b.active , '') active from t1 a left join t2 b on a.t1id = b.t2iddrop table t1,t2/*
name active
---------- ----------
aa 1
bb 2
cc (所影响的行数为 3 行)
*/
select a.name , isnull(c.activName '') active active from t1 a left join t2 b on a.t1id = b.t2id
left join t3 c on c.t3id=b.t2id
create table t1(T1id int,name varchar(10))
insert into t1 values(1,'aa')
insert into t1 values(2,'bb')
insert into t1 values(3,'cc')
create table t2 (T2id int,active varchar(10))
insert into t2 values(1,'1' )
insert into t2 values(2,'2' )
goselect a.name , isnull(b.active , '') active from t1 a left join t2 b on a.t1id = b.t2iddrop table t1,t2/*
name active
---------- ----------
aa 1
bb 2
cc (所影响的行数为 3 行)
*/