select * from subject a left join user b on a.writer=b.User_name where id in(select subject_id from reply where writer='某人')
select A.title, B.email from subject A left join user B on A.writer = B.id where B.name = @username union select A.title, B.email from subject A left join reply C on A.id = C.subject_id left join user B on C.writer = B.id where B.name = @username
我有看法,你那个表中字段是不是有问题。应该用subject_id 来建立表subject 与 reply的关系吧?关键键没有说清楚。! User(User_id, user_name, password, email) Subject(subject_id, title, body, writer) Reply(User_id, subject_id, body, writer) 语句: select title,email from User,Subject,Reply where User.User_id=Reply.User_id and Subject.Subject_id = Reply.Subject_id
select U.user_name,U.email,S.title from ((select id,email from User where user_name = @username) U inner join (select id,subject_id,writer from Reply) R on U.id =R.writer) ) A left join (select id,title from Subject) S on A.subject_id = S.id
如果想把个人在一个主题回复过多次,可加一个distinct。我的想法是实现效率最高。 select U.user_name,U.email,distinct S.title from ((select id,email from User where user_name = @username) U inner join (select id,subject_id,writer from Reply) R on U.id =R.writer)) A left join (select id,title from Subject) S on A.subject_id = S.id
where id in(select subject_id from reply where writer='某人')
union
select A.title, B.email from subject A left join reply C on A.id = C.subject_id left join user B on C.writer = B.id where B.name = @username
User(User_id, user_name, password, email)
Subject(subject_id, title, body, writer)
Reply(User_id, subject_id, body, writer)
语句:
select title,email from User,Subject,Reply where User.User_id=Reply.User_id and Subject.Subject_id = Reply.Subject_id
from ((select id,email from User where user_name = @username) U
inner join (select id,subject_id,writer from Reply) R on U.id =R.writer)
) A left join (select id,title from Subject) S on A.subject_id = S.id
select U.user_name,U.email,distinct S.title
from ((select id,email from User where user_name = @username) U inner join (select id,subject_id,writer from Reply) R on U.id =R.writer)) A left join (select id,title from Subject) S on A.subject_id = S.id