Not In Query 1

[byurow]

Ok, I have 2 tables. Tbl_IP_Address contains a listing of all IP Addresses assigned to my org. Tbl_MAC_IP associates the IP Address to the MAC address of a given device. I want to query for those IP Addresses not assigned to a MAC Address. Here is what I have so far:

SELECT Tbl_IP_Address.IP_Address_ID, Tbl_IP_Address.IP_Address
FROM Tbl_IP_Address LEFT JOIN Tbl_MAC_IP ON Tbl_IP_Address.IP_Address_ID = Tbl_MAC_IP.IP_Address_ID
WHERE (((Tbl_IP_Address.IP_Address_ID) Not In ([Tbl_MAC_IP].[IP_Address_ID])))
ORDER BY Tbl_IP_Address.IP_Address;

Any ideas why this query doesn't work? If I remove the Where Clause, the query returns all of my IP Addresses. Not sure why this doesn't work, hoping one of you guru's can point out where I am going wrong! Thanks!


Brenda

 

[PHV]

SELECT I.IP_Address_ID, I.IP_Address
FROM Tbl_IP_Address AS I
LEFT JOIN Tbl_MAC_IP AS M ON I.IP_Address_ID = M.IP_Address_ID
WHERE M.IP_Address_ID Is Null
ORDER BY I.IP_Address;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[byurow]

That did it...thank you!!!! I think I was looking at it too long!

Brenda