how do I use a value from the select in a joined subquery?

Question

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored procedure?

SELECT first_name,
       last_name,
       ip_address,
       ip_information.isp,
       count(*) duplicate_records
FROM   customers
LEFT OUTER JOIN (select * from ip_info.ip_address_data
                     where ip_to > INET_ATON(ip_address) limit 1
                ) ip_information using(ip_address)
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;

It's the LEFT OUTER JOIN that I'm trying to get data from a table that has ip address ranges and not exact ips.

CREATE TABLE `customers` (
  `customer_log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `ip_address` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `ip_address_data` (
    `ip_from` int(10) unsigned NOT NULL,
    `ip_to` int(10) unsigned NOT NULL DEFAULT '0',
    `country_code` char(2) DEFAULT NULL,
    `country_name` varchar(64) DEFAULT NULL,
    `region_name` varchar(128) DEFAULT NULL,
    `city_name` varchar(128) DEFAULT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `zip_code` varchar(30) DEFAULT NULL,
    `time_zone` varchar(8) DEFAULT NULL,
    `isp` varchar(256) DEFAULT NULL,
    `domain` varchar(128) DEFAULT NULL,
    `net_speed` varchar(8) DEFAULT NULL,
    `idd_code` varchar(5) DEFAULT NULL,
    `area_code` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`ip_to`),
    KEY `isp` (`isp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Explanation in words, I'm trying to select all the records from our customers and identify which ISP they're coming from. The main table is customers and the isp data is in the ip_info.ip_address_data table. The data in the customers table has the ip address the customer last used to login to the system. The other table doesn't have every ip address in the list, instead they have ranges of ips using ip_to and ip_from columns. Normally someone would do a query like this to get the data:

SELECT * FROM ip_info.ip_address_data
    WHERE ip_to   > INET_ATON('1.1.1.1')
      and ip_from < INET_ATON('1.1.1.1') 

But since I have the ip_to as the primary key it's much faster to just do ip_to > INET_ATON('1.1.1.1') and use limit so it stops returning records after the first row is found vs having to make sure it's < ip_from

Having the range in the table to join definitely makes the query more challenging.

INSERT INTO `customers` (`customer_log_id`, `customer_id`, `first_name`, `last_name`, `ip_address`, `created_at`)
VALUES
(1, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:47'),
    (2, 1, 'John', 'Smith', '1.0.0.2', '2016-03-11 11:59:52'),
    (3, 1, 'John', 'Smith', '1.0.0.3', '2016-03-11 11:59:53'),
    (4, 1, 'John', 'Smith', '1.0.0.4', '2016-03-11 11:59:55'),
    (5, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:48'),
    (6, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:49');


INSERT INTO `ip_address_data` (`ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`, `isp`, `domain`, `net_speed`, `idd_code`, `area_code`, `weather_station_code`, `weather_station_name`, `mcc`, `mnc`, `mobile_brand`, `elevation`, `usage_type`)
VALUES
(0, 16777215, '-', '-', '-', '-', 0, 0, '-', '-', 'Broadcast RFC1700', '-', '-', '-', '-', '-', '-', '-', '-', '-', 0, 'RSV'),
    (16777216, 16777471, 'AU', 'Australia', 'Queensland', 'Brisbane', -27.46794, 153.02809, '4000', '+10:00', 'Research Prefix for APNIC Labs', 'apnic.net', 'T1', '61', '07', 'ASXX0016', 'Brisbane', '-', '-', '-', 39, 'DCH'),
    (16777472, 16778239, 'CN', 'China', 'Fujian', 'Fuzhou', 26.06139, 119.30611, '350004', '+08:00', 'ChinaNet Fujian Province Network', 'chinatelecom.com.cn', 'DSL', '86', '0591', 'CHXX0031', 'Fuzhou', '460', '03', 'China Telecom', 12, 'ISP/MOB'),
    (16778240, 16778495, 'AU', 'Australia', 'Victoria', 'Melbourne', -37.814, 144.96332, '8010', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '03', 'ASXX0075', 'Melbourne', '-', '-', '-', 25, 'COM'),
    (16778496, 16779007, 'AU', 'Australia', 'New South Wales', 'Sydney', -33.86785, 151.20732, '2000', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '02', 'ASXX0112', 'Sydney', '-', '-', '-', 69, 'COM'),
    (16779008, 16779263, 'AU', 'Australia', 'Victoria', 'Melbourne', -37.814, 144.96332, '8010', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '03', 'ASXX0075', 'Melbourne', '-', '-', '-', 25, 'COM');

Answer 1

You probably have a bug: It should be ip_to >= instead of ip_to > -- to cover the case where ip_address is the same as ip_to.

You must make sure that there are no overlapping ranges in ip_information; otherwise you will occasionally get strange errors.

And, as Lee pointed out, you do need

and ipd.ip_from <= INET_ATON(c1.ip_address)

Otherwise, you could fall into a gap.

But, none of that addresses the question. You need a "correlated subquery", which cannot be done with a subquery in FROM/JOIN. So, let's do...

1. SELECT DISTINCT ip_address FROM customers ... This is an optimization so that we won't be repeatedly looking up the same ips.
2. Reach into ip_address_data to find a set of ip_to values.
3. Now reach back into both tables to get the rest of the info.

Something like this... (I have left out the necessary INET_ATON calls -- suggest you make your data consistent.)

First, Steps 1 and 2:

SELECT  c.ip_address, 
      ( SELECT  MIN(ip_to)
            FROM  ip_address_data
            WHERE  ip_to >= c.ip_address
            ORDER BY  ip_to
            LIMIT  1 
      ) AS ip_to
    FROM  
      ( SELECT  DISTINCT ip_address
            FROM  customers
            WHERE  ... 
      ) AS c;

See if that correctly delivers pairs of ip_address and ip_to. Yes, we have lost the customer info and ip_from; Step 3 will take care of that.

SELECT  c2..., a2...
    FROM  ( as_above ) AS b
    JOIN  customers AS c2 USING(ip_address)
    JOIN  ip_address_data AS a2 USING(ip_to)
    WHERE a2.ip_from <= b.ip_address
    GROUP BY ...
    ORDER BY ...;

Index needed:

customers: INDEX(ip_address)

One more thing... IPv6 is upon us; your table cannot handle such.

Answer 2

A couple of issues I can see right now. You are trying to join on one column to basically a value between two columns. You are also limiting the subquery to 1 row but you are trying to group by all the customer data so you can get a count of all the duplicates. Your counts, even if this worked would be 1 for everything. You can do this as a subquery, aliased, and with a new column in the select list with the ip address so that it can be joined to the customers table ip_address column.

SELECT c1.first_name,
   c1.last_name,
   c1.ip_address,
   c1.ip_information.isp,
   count(*) duplicate_records
FROM   customers c1
LEFT OUTER JOIN (select INET_NTOA(ipd.ip_to) ip_address from     ip_info.ip_address_data ipd
                 where ipd.ip_to >= INET_ATON(c1.ip_address)
and ipd.ip_from <= INET_ATON(c1.ip_address)
            ) ip_information using(ip_address)
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;

This leads to the column typing of the columns you are trying to join on. In customers ip_address is a varchar, but in ip_address_data the fields referenced is an int. I think you should think twice about joining these two tables, as it appears that the ip_address_data might be more broad than specifically to this one user. The core issue is that the select on the ip_address_data will run the function call on all the values in the table for all the data joined in order to generate a value that is suitable to join. You may want to clean the ip_address data more if in fact it is suitable for your needs (see next point).

Some ISPs use common IP addresses to represent all the users that subscribe to their service, so you might not be accurately joining the right references.