Here is my problem:
I have two tables, one table contians used IP addresses and one table contains all of the IP addresses my department has been assigned. What I would like to be able to do is to compare the two tables and return only the IP Addresses that have not been used yet. I have tried such things as the DISTINCT property, but have had no luck. All I get is duplicate data all throughout my query. Any SQL pro's out there ever attempted such a crazy thing? If so, what did you do to solve it? Any advice would be GREATLY appreciated!
First of all, force all of your IP addresses into the same format. I'd recommend minimal form (not permitting leading zeros within an octet, a dot between each octet, no leading or trailing blanks). So the "self" address would be formatted as "127.0.0.1" (obviously without the quotation marks) in minimal form. It doesn't really matter which form you use, as long as all of them use the same form.
Then build a table with all of the possible IP addresses within the range that you've been allocated. The easiest way to handle this is usually a nested loop solution to insert the rows. Remember to remove any rows that have been preallocated by network hardware (routers, etc) needed to support the rest of your IP range.
Finally, build a SELECT statement using the list of available IP addresses, and a NOT EXISTS against the IP addresses that you know have been used.