SEO

Im using MySQL version 5.1.22-rc-community.
I have downloaded the free version of MaxMinds GeoIP products, and is
having problems combing this into a new better structure.

I have imported the data from MaxMind into a table of their own. They
are:
CREATE TABLE `csv_cityblocks` (
`start` bigint(15) unsigned NOT NULL,
`end` bigint(15) unsigned NOT NULL,
`locid` bigint(15) unsigned NOT NULL,
PRIMARY KEY (`start`),
KEY `locid` (`locid`)
) ENGINE=MyISAM
I will refer to this table as "ccb".

CREATE TABLE `csv_citylocation` (
`locid` bigint(15) unsigned NOT NULL,
`cc` varchar(3) NOT NULL,
`region` varchar(3) NOT NULL,
`city` varchar(60) NOT NULL,
`postalCode` varchar(10) NOT NULL,
`latitude` double(10,6) NOT NULL,
`longitude` double(10,6) NOT NULL,
`dmaCode` smallint(3) NOT NULL,
`areaCode` smallint(3) NOT NULL,
PRIMARY KEY (`locid`),
KEY `city` (`cc`,`region`,`city`),
KEY `postalCode` (`postalCode`)
) ENGINE=MyISAM
I will refer to this table as "ccl".

CREATE TABLE `csv_country` (
`start_ip` varchar(15) NOT NULL,
`end_ip` varchar(15) NOT NULL,
`start` int(11) unsigned NOT NULL,
`end` int(11) unsigned NOT NULL,
`cc` varchar(3) NOT NULL,
`cn` varchar(50) NOT NULL,
PRIMARY KEY (`start`)
) ENGINE=MyISAM
I will refer to this table as "ccc".
Note that it does not have an ID field.

Table csv_country contains data from MaxMinds GeoIPCountryWhois.csv
file.
Table csv_cityblocks and csv_citylocation contains data from MaxMinds
GeoLiteCity-Blocks.csv and GeoLiteCity-Location.csv respectfully.
Keep in mind that these are 2 separate products and thus not designed
to work together!

I have created some tables to replace those above. My problem concern
2 of my own tables.
CREATE TABLE `ipLocation` (
`ipLocationID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`countryID` tinyint(3) unsigned DEFAULT NULL,
`locid` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`ipLocationID`),
KEY `countryID` (`countryID`),
) ENGINE=MyISAM
I will refer to this table as "il".
Note that `locid` is ONLY there to be used while converting from
MaxMinds structure to my own. When done it will be dropped as I will
then have my own ID, `ipLocationID`.

CREATE TABLE `ipBlocks` (
`start` bigint(20) unsigned NOT NULL,
`end` bigint(20) unsigned NOT NULL,
`ipLocationID` bigint(20) unsigned NOT NULL,
UNIQUE KEY `ipRange` (`start`,`end`),
KEY (`ipLocationID`),
KEY `start` (`start`),
KEY `end` (`end`)
) ENGINE=MyISAM
I will refer to this table as "ib".

With the table structures in place, you are ready to hear the problem
in detail.
The tables ccb and ccl work as a pair (joined by `locid`) where as ccc
work alone. `start` and `end` are numeric values of an IP address
range. ccc does have a human readable IP address range, namely
`start_ip` and `end_ip` but ignore these.

ccc have about 100k rows, ccb have about 2700k rows and ccl have about
165k rows.
I wish to combine the `start` and `end` from both ccc and ccb into one
big table, namely ib, and make it work as a pair with il (joined by
`ipLocationID`).

If I do a raw (no filtering) joining of ccc and ccb I will receive
duplicates, so I need to do filtering. I found that ccc only has about
2500 rows (unique `start` and `end` paris) that ccb does not have.

With me so far ?
If not, stop here and please ask your questions.

Good, you understand.

I have tried different things, such as creating a temp table and
inserted values from both ccb and ccc in order to import the data into
ccl (this is preferred as I use ccl for a number of operations, such
as linking an IP address range to a country and/or region and/or
city).

This is the table I used.
CREATE TABLE `tmp_blocks` (
`tmpid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cc` VARCHAR( 2 ) NOT NULL ,
`start` BIGINT UNSIGNED NOT NULL ,
`end` BIGINT UNSIGNED NOT NULL ,
KEY `tmpid` ( `tmpid` ),
KEY `ipRange` (`start`, `end`)
) ENGINE = MYISAM

I thought this would be a 3-step process.
Query1 - this will insert the majority of the IP address ranges. Plain
insert query
INSERT INTO `tmp_blocks` SELECT `csv_citylocation`.locid,cc,start,end
FROM `csv_citylocation`,`csv_cityblocks` WHERE
`csv_cityblocks`.locid=`csv_citylocation`.locid ORDER BY
`csv_cityblocks`.locid ASC

Query2 - this SHOULD insert unique `start`-`end` pairs not already in
the table from ccc. NOT WORKING
INSERT INTO `tmp_blocks` SELECT
NULL,`csv_country`.cc,`csv_country`.start,`csv_cou ntry`.end FROM
`csv_country`, `tmp_blocks` WHERE `csv_country`.start!
=`tmp_blocks`.start AND `csv_country`.end!=`tmp_blocks`.end ORDER BY
`csv_country`.start ASC

Query3 - this should then insert the cc and new ID value of the ccc
`start`-`end` pairs
INSERT IGNORE INTO `csv_citylocation` SELECT tmpid, countryID FROM
`tmp_blocks`, [country table] WHERE [stuff to find countryID from
`cc`]

With an ID linked to the IP address ranges from ccc I can continue.


Thats my problem. So am I doing it wrong, or is it just sql thats
flawed ?
Any other solution is also welcome.

On 25 Jan, 12:49, Kim <kims...@gmail.com> wrote:
> Im using MySQL version 5.1.22-rc-community.
> I have downloaded the free version of MaxMinds GeoIP products, and is
> having problems combing this into a new better structure.
>
> I have imported the data from MaxMind into a table of their own. They
> are:
> CREATE TABLE `csv_cityblocks` (
> `start` bigint(15) unsigned NOT NULL,
> `end` bigint(15) unsigned NOT NULL,
> `locid` bigint(15) unsigned NOT NULL,
> PRIMARY KEY (`start`),
> KEY `locid` (`locid`)
> ) ENGINE=MyISAM
> I will refer to this table as "ccb".
>
> CREATE TABLE `csv_citylocation` (
> `locid` bigint(15) unsigned NOT NULL,
> `cc` varchar(3) NOT NULL,
> `region` varchar(3) NOT NULL,
> `city` varchar(60) NOT NULL,
> `postalCode` varchar(10) NOT NULL,
> `latitude` double(10,6) NOT NULL,
> `longitude` double(10,6) NOT NULL,
> `dmaCode` smallint(3) NOT NULL,
> `areaCode` smallint(3) NOT NULL,
> PRIMARY KEY (`locid`),
> KEY `city` (`cc`,`region`,`city`),
> KEY `postalCode` (`postalCode`)
> ) ENGINE=MyISAM
> I will refer to this table as "ccl".
>
> CREATE TABLE `csv_country` (
> `start_ip` varchar(15) NOT NULL,
> `end_ip` varchar(15) NOT NULL,
> `start` int(11) unsigned NOT NULL,
> `end` int(11) unsigned NOT NULL,
> `cc` varchar(3) NOT NULL,
> `cn` varchar(50) NOT NULL,
> PRIMARY KEY (`start`)
> ) ENGINE=MyISAM
> I will refer to this table as "ccc".
> Note that it does not have an ID field.
>
> Table csv_country contains data from MaxMinds GeoIPCountryWhois.csv
> file.
> Table csv_cityblocks and csv_citylocation contains data from MaxMinds
> GeoLiteCity-Blocks.csv and GeoLiteCity-Location.csv respectfully.
> Keep in mind that these are 2 separate products and thus not designed
> to work together!
>
> I have created some tables to replace those above. My problem concern
> 2 of my own tables.
> CREATE TABLE `ipLocation` (
> `ipLocationID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `countryID` tinyint(3) unsigned DEFAULT NULL,
> `locid` bigint(20) unsigned DEFAULT NULL,
> PRIMARY KEY (`ipLocationID`),
> KEY `countryID` (`countryID`),
> ) ENGINE=MyISAM
> I will refer to this table as "il".
> Note that `locid` is ONLY there to be used while converting from
> MaxMinds structure to my own. When done it will be dropped as I will
> then have my own ID, `ipLocationID`.
>
> CREATE TABLE `ipBlocks` (
> `start` bigint(20) unsigned NOT NULL,
> `end` bigint(20) unsigned NOT NULL,
> `ipLocationID` bigint(20) unsigned NOT NULL,
> UNIQUE KEY `ipRange` (`start`,`end`),
> KEY (`ipLocationID`),
> KEY `start` (`start`),
> KEY `end` (`end`)
> ) ENGINE=MyISAM
> I will refer to this table as "ib".
>
> With the table structures in place, you are ready to hear the problem
> in detail.
> The tables ccb and ccl work as a pair (joined by `locid`) where as ccc
> work alone. `start` and `end` are numeric values of an IP address
> range. ccc does have a human readable IP address range, namely
> `start_ip` and `end_ip` but ignore these.
>
> ccc have about 100k rows, ccb have about 2700k rows and ccl have about
> 165k rows.
> I wish to combine the `start` and `end` from both ccc and ccb into one
> big table, namely ib, and make it work as a pair with il (joined by
> `ipLocationID`).
>
> If I do a raw (no filtering) joining of ccc and ccb I will receive
> duplicates, so I need to do filtering. I found that ccc only has about
> 2500 rows (unique `start` and `end` paris) that ccb does not have.
>
> With me so far ?
> If not, stop here and please ask your questions.
>
> Good, you understand.
>
> I have tried different things, such as creating a temp table and
> inserted values from both ccb and ccc in order to import the data into
> ccl (this is preferred as I use ccl for a number of operations, such
> as linking an IP address range to a country and/or region and/or
> city).
>
> This is the table I used.
> CREATE TABLE `tmp_blocks` (
> `tmpid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
> `cc` VARCHAR( 2 ) NOT NULL ,
> `start` BIGINT UNSIGNED NOT NULL ,
> `end` BIGINT UNSIGNED NOT NULL ,
> KEY `tmpid` ( `tmpid` ),
> KEY `ipRange` (`start`, `end`)
> ) ENGINE = MYISAM
>
> I thought this would be a 3-step process.
> Query1 - this will insert the majority of the IP address ranges. Plain
> insert query
> INSERT INTO `tmp_blocks` SELECT `csv_citylocation`.locid,cc,start,end
> FROM `csv_citylocation`,`csv_cityblocks` WHERE
> `csv_cityblocks`.locid=`csv_citylocation`.locid ORDER BY
> `csv_cityblocks`.locid ASC
>
> Query2 - this SHOULD insert unique `start`-`end` pairs not already in
> the table from ccc. NOT WORKING
> INSERT INTO `tmp_blocks` SELECT
> NULL,`csv_country`.cc,`csv_country`.start,`csv_cou ntry`.end FROM
> `csv_country`, `tmp_blocks` WHERE `csv_country`.start!
> =`tmp_blocks`.start AND `csv_country`.end!=`tmp_blocks`.end ORDER BY
> `csv_country`.start ASC
>
> Query3 - this should then insert the cc and new ID value of the ccc
> `start`-`end` pairs
> INSERT IGNORE INTO `csv_citylocation` SELECT tmpid, countryID FROM
> `tmp_blocks`, [country table] WHERE [stuff to find countryID from
> `cc`]
>
> With an ID linked to the IP address ranges from ccc I can continue.
>
> Thats my problem. So am I doing it wrong, or is it just sql thats
> flawed ?
> Any other solution is also welcome.

You could describe your problem in far fewer (and easier to
understand( lines, if you simple give sample data from the 2 tables
and showing the:
1) incorrect output that you get
2) output as you want to see it

On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> You could describe your problem in far fewer (and easier to
> understand( lines, if you simple give sample data from the 2 tables
> and showing the:
> 1) incorrect output that you get
> 2) output as you want to see it

I'll try to do that.

How do I join `start` and `end` from both csv_country and
csv_cityblocks to form a single table with no duplicates `start`-`end`
pairs + a new ID + locid (if any) ?


Sample data for tables:
csv_country
start_ip end_ip start end cc cn
2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
3.0.0.0 4.17.135.31 50331648 68257567 US United States
4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
4.17.135.64 4.17.142.255 68257600 68259583 US United States
4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
4.17.143.16 4.18.32.71 68259600 68296775 US United States
4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico

csv_cityblocks
start end locid
33996344 33996351 21604
50331648 67276831 223
67276832 67276847 4834
67276848 67277023 223
67277024 67277031 1002
67277032 67277039 223

csv_citylocation
locid cc region city postal latitude longitude dmacode areacode
223 US NULL NULL NULL 38.000000 -97.000000 0 0
1002 US VA Arlington 22202 38.860000 -77.053300 511 703
4834 US MA Boston 02114 42.361600 -71.067400 506 617
21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0

ipLocation
ipLocationID countryID locid
114577 231 223
116132 231 1002
119258 231 4834
68507 79 21604

ipBlocks is still empty, as Im trying to fill it, but this is how I
want it to show
123456870 123456875 114577
134567890 134567895 116132
145678900 145678908 119258
156789010 156789015 68507

On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
> On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > You could describe your problem in far fewer (and easier to
> > understand( lines, if you simple give sample data from the 2 tables
> > and showing the:
> > 1) incorrect output that you get
> > 2) output as you want to see it
>
> I'll try to do that.
>
> How do I join `start` and `end` from both csv_country and
> csv_cityblocks to form a single table with no duplicates `start`-`end`
> pairs + a new ID + locid (if any) ?
>
> Sample data for tables:
> csv_country
> start_ip end_ip start end cc cn
> 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> csv_cityblocks
> start end locid
> 33996344 33996351 21604
> 50331648 67276831 223
> 67276832 67276847 4834
> 67276848 67277023 223
> 67277024 67277031 1002
> 67277032 67277039 223
>
> csv_citylocation
> locid cc region city postal latitude longitude dmacode areacode
> 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> ipLocation
> ipLocationID countryID locid
> 114577 231 223
> 116132 231 1002
> 119258 231 4834
> 68507 79 21604
>
> ipBlocks is still empty, as Im trying to fill it, but this is how I
> want it to show
> 123456870 123456875 114577
> 134567890 134567895 116132
> 145678900 145678908 119258
> 156789010 156789015 68507

You haven't given any column heading for the output. I can't see where
for example: 134567890 134567895 has come from?

On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
>
>
> > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > You could describe your problem in far fewer (and easier to
> > > understand( lines, if you simple give sample data from the 2 tables
> > > and showing the:
> > > 1) incorrect output that you get
> > > 2) output as you want to see it
>
> > I'll try to do that.
>
> > How do I join `start` and `end` from both csv_country and
> > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > pairs + a new ID + locid (if any) ?
>
> > Sample data for tables:
> > csv_country
> > start_ip end_ip start end cc cn
> > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > csv_cityblocks
> > start end locid
> > 33996344 33996351 21604
> > 50331648 67276831 223
> > 67276832 67276847 4834
> > 67276848 67277023 223
> > 67277024 67277031 1002
> > 67277032 67277039 223
>
> > csv_citylocation
> > locid cc region city postal latitude longitude dmacode areacode
> > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > ipLocation
> > ipLocationID countryID locid
> > 114577 231 223
> > 116132 231 1002
> > 119258 231 4834
> > 68507 79 21604
>
> > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > want it to show
> > 123456870 123456875 114577
> > 134567890 134567895 116132
> > 145678900 145678908 119258
> > 156789010 156789015 68507
>
> You haven't given any column heading for the output. I can't see where
> for example: 134567890 134567895 has come from?

I wrote that at the question longer up, but I can say them again.
start end id old_id

On 28 Jan, 07:49, Kim <kims...@gmail.com> wrote:
> On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
> > > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > You could describe your problem in far fewer (and easier to
> > > > understand( lines, if you simple give sample data from the 2 tables
> > > > and showing the:
> > > > 1) incorrect output that you get
> > > > 2) output as you want to see it
>
> > > I'll try to do that.
>
> > > How do I join `start` and `end` from both csv_country and
> > > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > > pairs + a new ID + locid (if any) ?
>
> > > Sample data for tables:
> > > csv_country
> > > start_ip end_ip start end cc cn
> > > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > > csv_cityblocks
> > > start end locid
> > > 33996344 33996351 21604
> > > 50331648 67276831 223
> > > 67276832 67276847 4834
> > > 67276848 67277023 223
> > > 67277024 67277031 1002
> > > 67277032 67277039 223
>
> > > csv_citylocation
> > > locid cc region city postal latitude longitude dmacode areacode
> > > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > > ipLocation
> > > ipLocationID countryID locid
> > > 114577 231 223
> > > 116132 231 1002
> > > 119258 231 4834
> > > 68507 79 21604
>
> > > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > > want it to show
> > > 123456870 123456875 114577
> > > 134567890 134567895 116132
> > > 145678900 145678908 119258
> > > 156789010 156789015 68507
>
> > You haven't given any column heading for the output. I can't see where
> > for example: 134567890 134567895 has come from?
>
> I wrote that at the question longer up, but I can say them again.
> start end id old_id

I still cannot see where in your earlier tables the values 134567890
134567895 can have come from???

On Jan 28, 10:44 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 28 Jan, 07:49, Kim <kims...@gmail.com> wrote:
>
>
>
> > On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
> > > > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > You could describe your problem in far fewer (and easier to
> > > > > understand( lines, if you simple give sample data from the 2 tables
> > > > > and showing the:
> > > > > 1) incorrect output that you get
> > > > > 2) output as you want to see it
>
> > > > I'll try to do that.
>
> > > > How do I join `start` and `end` from both csv_country and
> > > > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > > > pairs + a new ID + locid (if any) ?
>
> > > > Sample data for tables:
> > > > csv_country
> > > > start_ip end_ip start end cc cn
> > > > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > > > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > > > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > > > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > > > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > > > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > > > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > > > csv_cityblocks
> > > > start end locid
> > > > 33996344 33996351 21604
> > > > 50331648 67276831 223
> > > > 67276832 67276847 4834
> > > > 67276848 67277023 223
> > > > 67277024 67277031 1002
> > > > 67277032 67277039 223
>
> > > > csv_citylocation
> > > > locid cc region city postal latitude longitude dmacode areacode
> > > > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > > > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > > > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > > > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > > > ipLocation
> > > > ipLocationID countryID locid
> > > > 114577 231 223
> > > > 116132 231 1002
> > > > 119258 231 4834
> > > > 68507 79 21604
>
> > > > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > > > want it to show
> > > > 123456870 123456875 114577
> > > > 134567890 134567895 116132
> > > > 145678900 145678908 119258
> > > > 156789010 156789015 68507
>
> > > You haven't given any column heading for the output. I can't see where
> > > for example: 134567890 134567895 has come from?
>
> > I wrote that at the question longer up, but I can say them again.
> > start end id old_id
>
> I still cannot see where in your earlier tables the values 134567890
> 134567895 can have come from???

Oh, I misunderstood you then.
The start and end values are fictitious. They were only meant to
illustrate the the structure and values of table ipBlocks.

On 28 Jan, 12:59, Kim <kims...@gmail.com> wrote:
> On Jan 28, 10:44 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 28 Jan, 07:49, Kim <kims...@gmail.com> wrote:
>
> > > On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
> > > > > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > > You could describe your problem in far fewer (and easier to
> > > > > > understand( lines, if you simple give sample data from the 2 tables
> > > > > > and showing the:
> > > > > > 1) incorrect output that you get
> > > > > > 2) output as you want to see it
>
> > > > > I'll try to do that.
>
> > > > > How do I join `start` and `end` from both csv_country and
> > > > > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > > > > pairs + a new ID + locid (if any) ?
>
> > > > > Sample data for tables:
> > > > > csv_country
> > > > > start_ip end_ip start end cc cn
> > > > > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > > > > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > > > > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > > > > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > > > > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > > > > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > > > > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > > > > csv_cityblocks
> > > > > start end locid
> > > > > 33996344 33996351 21604
> > > > > 50331648 67276831 223
> > > > > 67276832 67276847 4834
> > > > > 67276848 67277023 223
> > > > > 67277024 67277031 1002
> > > > > 67277032 67277039 223
>
> > > > > csv_citylocation
> > > > > locid cc region city postal latitude longitude dmacode areacode
> > > > > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > > > > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > > > > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > > > > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > > > > ipLocation
> > > > > ipLocationID countryID locid
> > > > > 114577 231 223
> > > > > 116132 231 1002
> > > > > 119258 231 4834
> > > > > 68507 79 21604
>
> > > > > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > > > > want it to show
> > > > > 123456870 123456875 114577
> > > > > 134567890 134567895 116132
> > > > > 145678900 145678908 119258
> > > > > 156789010 156789015 68507
>
> > > > You haven't given any column heading for the output. I can't see where
> > > > for example: 134567890 134567895 has come from?
>
> > > I wrote that at the question longer up, but I can say them again.
> > > start end id old_id
>
> > I still cannot see where in your earlier tables the values 134567890
> > 134567895 can have come from???
>
> Oh, I misunderstood you then.
> The start and end values are fictitious. They were only meant to
> illustrate the the structure and values of table ipBlocks.

I asked you to supply sample input data and theassociated output that
you wanted to see. You supplied sample input data and made up some
output that has no relation with the sample input.

How the H*LL do you expect that to be of any use!

On Jan 28, 2:28 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 28 Jan, 12:59, Kim <kims...@gmail.com> wrote:
>
>
>
> > On Jan 28, 10:44 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 28 Jan, 07:49, Kim <kims...@gmail.com> wrote:
>
> > > > On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
> > > > > > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > > > You could describe your problem in far fewer (and easier to
> > > > > > > understand( lines, if you simple give sample data from the 2 tables
> > > > > > > and showing the:
> > > > > > > 1) incorrect output that you get
> > > > > > > 2) output as you want to see it
>
> > > > > > I'll try to do that.
>
> > > > > > How do I join `start` and `end` from both csv_country and
> > > > > > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > > > > > pairs + a new ID + locid (if any) ?
>
> > > > > > Sample data for tables:
> > > > > > csv_country
> > > > > > start_ip end_ip start end cc cn
> > > > > > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > > > > > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > > > > > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > > > > > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > > > > > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > > > > > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > > > > > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > > > > > csv_cityblocks
> > > > > > start end locid
> > > > > > 33996344 33996351 21604
> > > > > > 50331648 67276831 223
> > > > > > 67276832 67276847 4834
> > > > > > 67276848 67277023 223
> > > > > > 67277024 67277031 1002
> > > > > > 67277032 67277039 223
>
> > > > > > csv_citylocation
> > > > > > locid cc region city postal latitude longitude dmacode areacode
> > > > > > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > > > > > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > > > > > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > > > > > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > > > > > ipLocation
> > > > > > ipLocationID countryID locid
> > > > > > 114577 231 223
> > > > > > 116132 231 1002
> > > > > > 119258 231 4834
> > > > > > 68507 79 21604
>
> > > > > > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > > > > > want it to show
> > > > > > 123456870 123456875 114577
> > > > > > 134567890 134567895 116132
> > > > > > 145678900 145678908 119258
> > > > > > 156789010 156789015 68507
>
> > > > > You haven't given any column heading for the output. I can't see where
> > > > > for example: 134567890 134567895 has come from?
>
> > > > I wrote that at the question longer up, but I can say them again.
> > > > start end id old_id
>
> > > I still cannot see where in your earlier tables the values 134567890
> > > 134567895 can have come from???
>
> > Oh, I misunderstood you then.
> > The start and end values are fictitious. They were only meant to
> > illustrate the the structure and values of table ipBlocks.
>
> I asked you to supply sample input data and theassociated output that
> you wanted to see. You supplied sample input data and made up some
> output that has no relation with the sample input.
>
> How the H*LL do you expect that to be of any use!

Geez.. I wrote both a detailed problem and a simple with sample data
(I guess you didnt read the detailed one). The result I need, has
never changed.
I cant show the associated output because it doesnt exist yet! Thats
the problem!
I cant show manual paired end-result based on the limited sample, if I
did show you an actual end-result you would ask "where the h*** did
that data come from?", hence the fictitious start and end values.

The sample data I have shown are linked together. In the detailed post
I showed some queries I used.

Guess I have to find another place to seek help.

On 28 Jan, 14:46, Kim <kims...@gmail.com> wrote:
> On Jan 28, 2:28 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 28 Jan, 12:59, Kim <kims...@gmail.com> wrote:
>
> > > On Jan 28, 10:44 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 28 Jan, 07:49, Kim <kims...@gmail.com> wrote:
>
> > > > > On Jan 25, 4:10 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > > On 25 Jan, 13:49, Kim <kims...@gmail.com> wrote:
>
> > > > > > > On Jan 25, 1:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > > > > You could describe your problem in far fewer (and easier to
> > > > > > > > understand( lines, if you simple give sample data from the 2 tables
> > > > > > > > and showing the:
> > > > > > > > 1) incorrect output that you get
> > > > > > > > 2) output as you want to see it
>
> > > > > > > I'll try to do that.
>
> > > > > > > How do I join `start` and `end` from both csv_country and
> > > > > > > csv_cityblocks to form a single table with no duplicates `start`-`end`
> > > > > > > pairs + a new ID + locid (if any) ?
>
> > > > > > > Sample data for tables:
> > > > > > > csv_country
> > > > > > > start_ip end_ip start end cc cn
> > > > > > > 2.6.190.56 2.6.190.63 33996344 33996351 GB United Kingdom
> > > > > > > 3.0.0.0 4.17.135.31 50331648 68257567 US United States
> > > > > > > 4.17.135.32 4.17.135.63 68257568 68257599 CA Canada
> > > > > > > 4.17.135.64 4.17.142.255 68257600 68259583 US United States
> > > > > > > 4.17.143.0 4.17.143.15 68259584 68259599 CA Canada
> > > > > > > 4.17.143.16 4.18.32.71 68259600 68296775 US United States
> > > > > > > 4.18.32.72 4.18.32.79 68296776 68296783 MX Mexico
>
> > > > > > > csv_cityblocks
> > > > > > > start end locid
> > > > > > > 33996344 33996351 21604
> > > > > > > 50331648 67276831 223
> > > > > > > 67276832 67276847 4834
> > > > > > > 67276848 67277023 223
> > > > > > > 67277024 67277031 1002
> > > > > > > 67277032 67277039 223
>
> > > > > > > csv_citylocation
> > > > > > > locid cc region city postal latitude longitude dmacode areacode
> > > > > > > 223 US NULL NULL NULL 38.000000 -97.000000 0 0
> > > > > > > 1002 US VA Arlington 22202 38.860000 -77.053300 511 703
> > > > > > > 4834 US MA Boston 02114 42.361600 -71.067400 506 617
> > > > > > > 21604 GB C3 Cambridge NULL 52.200000 0.116700 0 0
>
> > > > > > > ipLocation
> > > > > > > ipLocationID countryID locid
> > > > > > > 114577 231 223
> > > > > > > 116132 231 1002
> > > > > > > 119258 231 4834
> > > > > > > 68507 79 21604
>
> > > > > > > ipBlocks is still empty, as Im trying to fill it, but this is how I
> > > > > > > want it to show
> > > > > > > 123456870 123456875 114577
> > > > > > > 134567890 134567895 116132
> > > > > > > 145678900 145678908 119258
> > > > > > > 156789010 156789015 68507
>
> > > > > > You haven't given any column heading for the output. I can't see where
> > > > > > for example: 134567890 134567895 has come from?
>
> > > > > I wrote that at the question longer up, but I can say them again.
> > > > > start end id old_id
>
> > > > I still cannot see where in your earlier tables the values 134567890
> > > > 134567895 can have come from???
>
> > > Oh, I misunderstood you then.
> > > The start and end values are fictitious. They were only meant to
> > > illustrate the the structure and values of table ipBlocks.
>
> > I asked you to supply sample input data and theassociated output that
> > you wanted to see. You supplied sample input data and made up some
> > output that has no relation with the sample input.
>
> > How the H*LL do you expect that to be of any use!
>
> Geez.. I wrote both a detailed problem and a simple with sample data
> (I guess you didnt read the detailed one). The result I need, has
> never changed.
> I cant show the associated output because it doesnt exist yet! Thats
> the problem!
> I cant show manual paired end-result based on the limited sample, if I
> did show you an actual end-result you would ask "where the h*** did
> that data come from?", hence the fictitious start and end values.
>
> The sample data I have shown are linked together. In the detailed post
> I showed some queries I used.
>
> Guess I have to find another place to seek help.

If thouse output field do not derive directly from datain the input
tables then there is no way to get the output you require.
If they do derive from the input data that you have posted, pelase
explain HOW they are derived.