多表联查时两表关联第三表同一ID的问题解决方法

多表联查时两表关联第三表同一ID的问题解决方法

 

 

用示例来说明问题更好些，可以参照下面的关系图和代码来帮助理解。

 

只是在原有基础上再添加一个BBSUsers表的别名引用就行了。问题也很简单，就不多说了！

 

 

数据库关系图如下：

 

 

 

代码如下：

 

--根据固定的ID值，查询显示主帖的标题、作者姓名、内容，所有的跟帖的标题、跟帖人的姓名、跟帖内容、跟帖时间
Select T.TTopic As '主帖标题', TU.UName As '作者姓名', T.TContents As '内容',
       R.RTopic As '跟帖标题', RU.UName As '跟帖人姓名', R.RContents As '跟帖内容', R.RTime As '跟帖时间'
From BBSTopic As T, BBSReply As R, BBSUsers As TU, BBSUsers As RU
Where T.TID = R.RTID And TU.UID = T.TUID And RU.UID = R.RUID And T.TID = 2
Order By R.RTime
--或：
Select T.TTopic As '主帖标题', TU.UName As '作者姓名', T.TContents As '内容',
       R.RTopic As '跟帖标题', RU.UName As '跟帖人姓名', R.RContents As '跟帖内容', R.RTime As '跟帖时间'
From BBSTopic As T
Inner Join BBSReply As R
On T.TID = R.RTID
Inner Join BBSUsers As TU
On TU.UID = T.TUID
Inner Join BBSUsers As RU
On RU.UID = R.RUID
Where T.TID = 2
Order By R.RTime