SQL IN syntax. Natural join, maybe?

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

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.

Re: SQL IN syntax. Natural join, maybe?

SELECT ip FROM db1.table1 WHERE ip IN ( SELECT
substring(ip_network,0,6) FROM
db2.table2 WHERE name = )

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.

Re: SQL IN syntax. Natural join, maybe?

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'.

Re: SQL IN syntax. Natural join, maybe?

>SELECT ip FROM db1.table1 WHERE ip IN ( SELECT ip_network FROM
>db2.table2 WHERE name = )
>
>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 = )

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

Re: SQL IN syntax. Natural join, maybe?

This seems to be getting closer, however, I still get an error after
the IN.

I'm using MySQL 4.0.1.