如何从3个不同的数据表中获取数据? [英] How to get data from 3 different data tables?
问题描述
我的数据库中有3个表 tblUserInfo,tblVisitor ,第三个表 tblInterest
表 tblUserInfo 包含用户信息以及主键UserID。
表 tblVisitor 包含 VisitorId 和 VistedProfileId
VisitorId和VisitedProfileId除了tblUserInfo中的UserID
表 tblInterest 包含< b> InterestFrom_UserID 和 InterestIn_UserID 和 InterestStatus
我想从t blUserInfo 获得用户的详细信息,
但是那些用户在 tblVisitor
并获得 InterestStatus 来自 tblInterest
表 tblInterest 可能包含也可能不包含< b> VisitorID
我想要
如果表 tblInteres 不包含与 visitorID 的记录匹配,那么我想要 InterestStatus 字段中的空值。
我尝试过:
我的查询如下
I have 3 tables in my Database tblUserInfo, tblVisitor and third one tblInterest
table tblUserInfo Contains User Information along with primary key UserID.
table tblVisitor contains VisitorId and VistedProfileId
VisitorId and VisitedProfileId nothing but UserID in tblUserInfo
Table tblInterest Contain InterestFrom_UserID and InterestIn_UserID and InterestStatus
I want to get Details of user from tblUserInfo,
But those user are in tblVisitor
And get InterestStatus from tblInterest
the table tblInterest may or may not contain record matches to VisitorID
I want
if table tblInteres not contain record matches to visitorID then I want null value in InterestStatus Field.
What I have tried:
My query as follow
SELECT tblUserInfo.UserAutoID, tblVisitor.VisitedProfileUserAutoId, tblUserInfo.UserFirstName, tblUserInfo.ProfileID,
YEAR(GETDATE())-YEAR(UserDOB) AS UserDOB,tblUserInfo.Height,tblUserInfo.Religion, tblUserInfo.Education,
tblUserInfo.Occupation, tblUserInfo.ProfilePicPath, tblInterest.InterestStatus
FROM
tblVisitor INNER JOIN tblUserInfo
ON
tblVisitor.VisitorAutoID=tblUserInfo.UserAutoID
JOIN tblInterest
ON
tblVisitor.VisitorAutoID=tblInterest.InterestIn
WHERE
tblVisitor.VisitedProfileUserAutoId=@VisitedProfileUserAutoId
AND
tblVisitor.VisitorAutoID=tblInterest.InterestIn
AND
tblVisitor.IsDeleted=0
当我触发查询时,我只得到那些在tblInteres
When I fire above query I get only those recored that are in tblInteres
推荐答案
问题:你有VisitorID和UserID。那么为什么你使用了VisitorAutoID和UserAutoID?
解决方案:删除连接并添加左连接。 ( JOIN tblInterest
Question : you have VisitorID and UserID. then why have you used VisitorAutoID and UserAutoID ?
Solution : Remove join And add Left join. (JOIN tblInterest
LEFT JOIN tblInterest
)
这篇关于如何从3个不同的数据表中获取数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!