JOIN based on LIKE condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOIN based on LIKE condition

Author

Topic

exsurgo
Starting Member

5 Posts

Posted - 2008-04-06 : 18:52:40

I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join.

Table 1 -> TRAFFIC (Time, SourceIP)
Table 2 -> IP_ROSTER (IP2OctetRange, Administrator)

I've tried the following, but it does not seem to work:


SELECT TOP 100
	SOURCE_IP,
	r.IP2OctetRange,
	r.Administrator
FROM TRAFFIC
	LEFT JOIN IP_ROSTER AS r 
	ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-06 : 19:18:13

Can you be more specific - when you use that query what doesn't work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 00:49:49

Your query doesnt look to have any problem. Can you post more info on your error?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-04-07 : 01:25:41

can u try like this.
SELECT TOP 100
SOURCE_IP,
r.IP2OctetRange,
r.Administrator
FROM TRAFFIC
LEFT JOIN IP_ROSTER AS r
ON SOURCE_IP LIKE RTRIM(LTRIM('IP2OctetRange%'))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 04:04:49

[code]DECLARE @Traffic TABLE (Time DATETIME, SourceIP VARCHAR(15))

INSERT @Traffic
SELECT GETDATE(), '166.27.12.24' UNION ALL
SELECT GETDATE(), '166.28.177.119'

DECLARE @Rooster TABLE (IP2OctetRange VARCHAR(7), Administrator TINYINT)

INSERT @Rooster
SELECT '166.27', 0

SELECT TOP 100 t.SourceIP,
r.IP2OctetRange,
r.Administrator
FROM @Traffic AS t
LEFT JOIN @Rooster AS r ON t.SourceIP LIKE r.IP2OctetRange + '%'[/code]

E 12°55'05.25"
N 56°04'39.16"

exsurgo
Starting Member

5 Posts

Posted - 2008-04-07 : 11:49:49

quote:
Originally posted by snSQL

Can you be more specific - when you use that query what doesn't work?

The query does not produce an error, it just does not produce the results that I need. The full IP in 'TRAFFIC' table is not matched at all with the partial IP range in 'IP_ROSTER' table.

exsurgo
Starting Member

5 Posts

Posted - 2008-04-07 : 11:50:36

quote:
Originally posted by soorajtnpki

can u try like this.
SELECT TOP 100
SOURCE_IP,
r.IP2OctetRange,
r.Administrator
FROM TRAFFIC
LEFT JOIN IP_ROSTER AS r
ON SOURCE_IP LIKE RTRIM(LTRIM('IP2OctetRange%'))

The query has the same result as the original, in that no records are matched together.

exsurgo
Starting Member

5 Posts

Posted - 2008-04-07 : 12:21:33

quote:

Originally posted by Peso

DECLARE	@Traffic TABLE (Time DATETIME, SourceIP VARCHAR(15))

INSERT	@Traffic
SELECT	GETDATE(), '166.27.12.24' UNION ALL
SELECT	GETDATE(), '166.28.177.119'

DECLARE	@Rooster TABLE (IP2OctetRange VARCHAR(7), Administrator TINYINT)

INSERT	@Rooster
SELECT	'166.27', 0

SELECT TOP 100	t.SourceIP,
		r.IP2OctetRange,
		r.Administrator
FROM		@Traffic AS t
LEFT JOIN	@Rooster AS r ON t.SourceIP LIKE r.IP2OctetRange + '%'

E 12°55'05.25"
N 56°04'39.16"

Hi Peso, this present an interesting solution, but I cannont seem to figure out how to make this work with two existing tables. I've tried the following, but still no records are matched.


DECLARE	@Traffic TABLE (FRAME_TIME DATETIME, SourceIP VARCHAR(15))

INSERT	@Traffic
SELECT FRAME_TIME, SOURCE_IP FROM TRAFFIC 
WHERE FRAME_TIME BETWEEN '1/22/08 10:00AM' AND '1/23/08 11:30AM'

DECLARE	@Rooster TABLE (IP2OctetRange VARCHAR(8), Administrator VARCHAR(50))

INSERT	@Rooster
SELECT	RTRIM(LTRIM(IP2OctetRange)), Administrator FROM IP_ROSTER

SELECT t.SourceIP,
		r.IP2OctetRange,
		r.Administrator,
		RTRIM(LTRIM(IP2OctetRange))
FROM		@Traffic AS t
LEFT JOIN	@Rooster AS r ON t.SourceIP LIKE r.IP2OctetRange + '%'

WHERE t.FRAME_TIME BETWEEN '1/22/08 10:00AM' AND '1/23/08 11:30AM'
AND IP2OctetRange IS NOT NULL

exsurgo
Starting Member

5 Posts

Posted - 2008-04-07 : 17:24:25

Okay after several hours, I've finally figured out this issue. The other JOIN with a LIKE actually worked fine, but additional spaces were imported with the data and the RTRIM(LTRIM(IP2OctetRange)) function, for whatever reason, was not removing the spaces.

After reimporting the data with spaces removed, the queries worked as expected.

Thanks very much for the assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources