CODEWITHSUNDEEP
sql-serverinner-join
user3932611
user3932611

Reputation:

SQL Server returning individual column from an inner join

I have the following SQL query:

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

What I want it to do is return only the displayname from table IPAddress.

I have tried

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

but it says some of the objects have the same exposed names.

I have tried everything I can think of but cant work this out

Upvotes: 0

Views: 58

Answers (2)

Joachim Sauer
Joachim Sauer

Reputation: 505

Try this one:

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

Upvotes: 0

Milen
Milen

Reputation: 8867

Try this:

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

Upvotes: 1

Related Questions