如何解决这个问题??
现有三表:
表一,address
classuuid truename username
123 Deng Adi
123 Wang Jame
表二 leavemsg
classuuid author
123 Adi
123 Jame
123 Hank
现在还另有一表:classinfo
classuuid default
123 Yes
234 NO
345 NO
现在查询结果要如下所示:
classuuid truename
123 Deng
123 Wang
123 Hank
查询语句如下:
SELECT leavemsg.classuuid, iif(isnull(address.truename), leavemsg.author,address.truename) as truename,leavemsg.datetime,leavemsg.message
FROM (address right JOIN leavemsg ON address.classuuid = leavemsg.classuuid AND address.username=leavemsg.author) inner join classinfo on classinfo.classuuid=leavemsg.classuuid
Where Classinfo.default=Yes;
现想将查询结果中truename为Hank的记录找出来,请问要如何写?
SELECT leavemsg.classuuid, iif(isnull(address.truename), leavemsg.author,address.truename) as truename,leavemsg.datetime,leavemsg.message
FROM (address right JOIN leavemsg ON address.classuuid = leavemsg.classuuid AND address.username=leavemsg.author) inner join classinfo on classinfo.classuuid=leavemsg.classuuid
WHERE [leaveMsg].[classuuid]='123' and truename='Hank'
ORDER BY LeaveMsg.datetime DESC;
像这样的语句找不出Hank记录,只能找出truename为Deng或Wang的记录。