Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Question Unanswered: Comparing 2 tables

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Thumbs up

    That worked like a charm! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •