470,604 Members | 2,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,604 developers. It's quick & easy.

SQL IN syntax. Natural join, maybe?

SELECT ip FROM db1.table1 WHERE ip IN ( SELECT ip_network FROM
db2.table2 WHERE name = <value> )

Basically, the way the data shows up in the first column 'ip' is
x.x.x.x (full ip). However, for ip_network, it is x.x (only the first
two octets).

My question is, are there any ways to do some sort of wildcard search
so that I could match the partial IP from ip_network up to however many
full IPs there are in my first table. It would be a 1:n relationship
(I believe ... I don't have it neccessarily setup w. FKs. This is a
temp. project and this could save me a lot of coding time).

I wouldn't doubt it if it could be accomplished by a JOIN or something
of that nature. I'm just not sure because the two values won't be
exactly equal. Any comments are welcome. Thanks.

Apr 21 '06 #1
4 3008
SELECT ip FROM db1.table1 WHERE ip IN ( SELECT
substring(ip_network,0,6) FROM
db2.table2 WHERE name = <value> )

now, depending on how you store the octets (as left-zero padded or not)
will give you varying results that you may need to resolve using a
programming/scripting language.
I would review the docs on string manipulation.

You could always break the column into octets or just add octet columns
- so that when dealing with IPV6 you won't be getting the wrong
results.

Apr 21 '06 #2
IPv6 wont be an issue for this project. The way they are stored is
simply as you would seem them in modern format (10.1.1.1).

I'm wanting to match one row of '10.1' to all other rows from the
other table that begin with '10.1'.

Apr 21 '06 #3
>SELECT ip FROM db1.table1 WHERE ip IN ( SELECT ip_network FROM
db2.table2 WHERE name = <value> )

Basically, the way the data shows up in the first column 'ip' is
x.x.x.x (full ip). However, for ip_network, it is x.x (only the first
two octets).

My question is, are there any ways to do some sort of wildcard search
so that I could match the partial IP from ip_network up to however many
full IPs there are in my first table. It would be a 1:n relationship
(I believe ... I don't have it neccessarily setup w. FKs. This is a
temp. project and this could save me a lot of coding time).


So compute something suitable from ip that will EXACTLY match
what you have in ip_network. For example, if ip is 192.168.2.3, match
against 192.168 . I suggest:

SELECT ip FROM db1.table1 WHERE substring_index(ip, '.', 2) IN
( SELECT ip_network FROM db2.table2 WHERE name = <value> )

although I have not tested this. Sometimes this sort of thing
involves a nested mess of string functions that search for the position
of things, then use the position to slice off a piece of the string.

Gordon L. Burditt
Apr 21 '06 #4
This seems to be getting closer, however, I still get an error after
the IN.

I'm using MySQL 4.0.1.

Apr 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

699 posts views Thread by mike420 | last post: by
4 posts views Thread by MAB | last post: by
4 posts views Thread by Toonman | last post: by
2 posts views Thread by Martin | last post: by
8 posts views Thread by Ike | last post: by
177 posts views Thread by C# Learner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.