多表查询问题 left，rigth join 问题

[wxlwxlwxlwxl]

是这样两个表 tfile（fileid，filename，filepath，subjectid），tsubject（subjectid，subjectname）
假定 tfile表数据为
fileid filename filepath subjectid
1 file1 img/file1.jpg 1
2 file2 img/file2.jpg 1
3 file3 img/file3.jpg 1

tsubject表数据为
subjectid subjectname
1 photo
2 life

我要实现这种效果

fileid filename filepath subjectid subjectname
3 file3 img/file3.jpg 1 photo
null null null 2 life

我现在只是实现了
fileid filename filepath subjectid subjectname
3 file3 img/file3.jpg 1 photo

我是这样写的
select fileid,filepath,filename,subjectid,subjectname from tfile a right join tsubject b on a.subjectid =b.subjectid where a.fileid in
( select max(fileid) from tfile c inner join tsubject d on c.subjectid =d.subjectid group by c.subjectid )

这个问题怎么解决啊？

[-狙击手-]

我要实现这种效果 

fileid  filename  filepath        subjectid  subjectname 
3       file3    img/file3.jpg    1          photo 
null    null     null             2          life 


---

select b.fileid,
       b.filename,
       b.filepath,
       a.*
from tsubject a
left join tfile b  on a.subjectid = b.subjectid

[pt1314917]

select c.fileid,c.filename,c.filepath,b.* from 
(select * from tfile a where not exists(select 1 from tfile where subjectid=a.subjectid and fileid>a.fileid))c right join tsubject b 
on c.subjectid = b.subjectid 

[-狙击手-]

select b.fileid,
       b.filename,
       b.filepath,
       a.*
from select * 
     from tsubject t
     where not exists(select 1 from tsubject where subjectid = t.subjectid and fileid> t.fileid)  a
left join tfile b  on a.subjectid = b.subjectid

[kk19840210]

create table tfile(fileid int,[filename] varchar(10),filepath varchar(20),subjectid int)


create table tsubject(subjectid int ,subjectname varchar(10)) 

insert into tfile values(1,'file1','img/file1.jpg',1)
insert into tfile values(2,'file2','img/file2.jpg',1)
insert into tfile values(3,'file3','img/file3.jpg',1)

insert into tsubject values(1,'photo')
insert into tsubject values(2,'life')


select  fileid,filepath,[filename],c.subjectid,subjectname from tsubject c left join
(select * from tfile a where not exists(select 1 from tfile where fileid>a.fileid))d
on c.subjectid=d.subjectid 



fileid      filepath             filename   subjectid   subjectname
----------- -------------------- ---------- ----------- -----------
3           img/file3.jpg        file3      1           photo
NULL        NULL                 NULL       2           life

(2 行受影响)

[-狙击手-]

select b.fileid,
       b.filename,
       b.filepath,
       a.*
from (select * 
     from tsubject t
     where not exists(select 1 from tsubject where subjectid = t.subjectid and fileid> t.fileid))  a
left join tfile b  on a.subjectid = b.subjectid

[山之魂2]

select b.fileid,b.filename, b.filepath,a.*
from tfile b
right join tsubject a
on a.subjectid = b.subjectid

[pt1314917]

小枪，你那连接不对吧？应该换右连接。

[山之魂2]

怎么把SQL 里面的文字颜色带出来啊?

[pt1314917]

在SQL语句后面加上“我要带颜色显示”即可，如：

select * from 表名 我要带颜色显示

[nzperfect]

create table tfile(fileid int,filename varchar(20),filepath varchar(30),subjectid int)
insert into tfile
select 1       ,'file1',    'img/file1.jpg',    1 union all
select 2       ,'file2',    'img/file2.jpg',    1 union all
select 3       ,'file3',    'img/file3.jpg',    1 

create table tsubject(subjectid int ,subjectname varchar(20))
insert into tsubject
select 1          ,'photo' union all
select 2          ,'life' 


select b.fileid,b.filename,b.filepath,
a.subjectid,a.subjectname
from tsubject as a 
left join 
(
select fileid,filename,filepath,subjectid from tfile as c where fileid=(select max(fileid) from tfile where subjectid=c.subjectid)
)as b on a.subjectid=b.subjectid

--
3	file3	img/file3.jpg	1	photo
NULL	NULL	NULL	2	life

[flyidealism]

---建立测试数据
create table tfile(fileid int,filename varchar(100),filepath varchar(50),subjectid int)

insert into tfile
select 1 ,'file1','img/file1.jpg',1
union all
select 2,'file2','img/file2.jpg',1
union all
select 3,'file3','img/file3.jpg',1


create table tsubject(subjectid int,subjectname varchar(50))
insert into tsubject
select 1,'photo'
union all
select 2,'life'

--SQL代码实现
select a.fileid,a.filename,a.filepath,b.subjectid,b.subjectname
from
(select * from tfile
where fileid=3) a right join tsubject b
on a.subjectid=b.subjectid
--结果如你要求

[wxlwxlwxlwxl]

多谢各位，我先试下。

[wxlwxlwxlwxl]

解决了，多谢各位
我把我那个稍微改了下也没问题了
：）