Join two tables on a calculated field

[learner45]

Hi Everybody,

Question from a novice

I have 2 tables:

1. tblUsers
Fields: UserId, UserName, FromIP, ToIP, Country

User ID is PK (unique); FromIP and ToIP are the IP addresses in notation (123.28.47.227) etc. defining the network range of users.

2. tblSessions
Fields: IP, Sessions

What I'm trying to do is create a view that will show the following info:

UserId, UserName, Sessions

i.e. it will match the tblSessions.IP field with the tblUsers.FromIP & tblUsers.ToIP by a 'BETWEEN' Clause...

 

[gmmastros]

You can use a between condition in an on clause, like this...

Select tblUsers.UserId, tblUsers.UserName, tblSessions.Sessions
From   tblUsers
       Inner Join tblSessions
         On tblSession.IP Between tblUsers.FromIP And tblUsers.ToIP

This would absolutely work out well for you if IP Addresses were values that compared well to other values. Unfortunately, they're not. If I had to guess, I would say that you are storing ip addresses in varchar columns. As such, the between operator will use string comparisons for determining what is in your range.

There are a couple of ways to handle this. You could make sure that each octet is exactly 3 characters (so 123.28.47.227 would be stored as 123.[!]0[/!]28.[!]0[/!]47.227). Alternatively, you could convert your IP Addresses to a bigint and compare them that way.

Here's a blog that explains how you can do this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-convert-ip-addresses-between-bigi

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[learner45]

Thank you so much!

It indeed solved the problem....although I,m scratching my head over 'GROUP BY' since the customers are appearing multiple times in the created view :-(