SQL Server 从内部联接返回单个列

Question

>我有以下SQL查询：

select 
   * 
from 
   tbl_alert
inner join 
   tbl_IPAddress on tbl_IPAddress.ID = tbl_alert.IPAddressID
where
   tbl_alert.AlertType_ClassID = 124
   and tbl_ipaddress.ID = 4
   and DATEDIFF(minute, ISNULL((select max(scandatetime) 
                                from TBL_ScanQueue 
                                where IPAddress = tbl_ipaddress.IPAddress), GETDATE()), 0) < tbl_alert.Inactivity_Alert_Time

我希望它做的是仅返回表 IPAddress 中的displayname。

我试过了

select 
    Displayname 
from 
    tbl_IPAddress
inner join 
    tbl_IPAddress on tbl_IPAddress.ID = tbl_alert.IPAddressID
where
    tbl_alert.AlertType_ClassID = 124
    and tbl_ipaddress.ID = 4
    and DATEDIFF(minute, ISNULL((select max(scandatetime) 
                                 from TBL_ScanQueue 
                                 where IPAddress = tbl_ipaddress.IPAddress), GETDATE()), 0) < tbl_alert.Inactivity_Alert_Time

但它说一些对象具有相同的公开名称。

我已经尝试了我能想到的一切，但无法解决这个问题

Answer 1

试试这个：

select Displayname 
from tbl_IPAddress
inner join tbl_alert on tbl_IPAddress.ID = tbl_alert.IPAddressID
where
tbl_alert.AlertType_ClassID = 124
and 
tbl_ipaddress.ID = 4
and
DATEDIFF(minute, ISNULL((select max(scandatetime) from TBL_ScanQueue where IPAddress = tbl_ipaddress.IPAddress), GETDATE()), 0) < tbl_alert.Inactivity_Alert_Time

Answer 2

试试这个：

SELECT tbl_IPAddress.Displayname
FROM tbl_alert
INNER JOIN tbl_IPAddress 
ON tbl_IPAddress.ID = tbl_alert.IPAddressID
WHERE tbl_alert.AlertType_ClassID = 124
AND tbl_ipaddress.ID = 4
AND DATEDIFF(minute, ISNULL((select max(scandatetime) from TBL_ScanQueue where IPAddress = tbl_ipaddress.IPAddress), GETDATE()), 0) < tbl_alert.Inactivity_Alert_Time