CODEWITHSUNDEEP
phpmysql
Shopmaster
Shopmaster

Reputation: 94

Finding data overlaps in 1 field in two tables [MySQL]

I have a table "site_ipv4". It has a ip_binary field. I need to compare the ip_binary representations of the records at the specific ip_network_length to see if it matches another with an id_status = “A” record in the same table. If they match, write them in a report.

For example, let’s say you have three site licenses with ipv4 addresses:

user_id | id_status | ip_binary                        | ip_network_length
800AA   | A         | 10000011111001100100101011110010 | 32
800AB   | A         | 10000011111001100100101000000000 | 24
800AC   | X         | 10000011111001100000000000000000 | 16

(1) Looking at the first one, the 32 bit length means you have the whole string (“10000011111001100100101011110010”). This doesn’t match the second account. The third account is id_status = “X” so you can ignore it.

(2) Looking at the second one, the 24-bit length means you have the shorter string (“100000111110011001001010”). This matches the first 24 bits of the first account so you’d have to report that there’s an overlap here. The third account is id_status = “X” so you can ignore it.

(3) Looking at the third one, it’s id_status = “X” so you can ignore it.

How can I compare the fields to find the overlapping ip_binary?

Upvotes: 0

Views: 57

Answers (1)

Strawberry
Strawberry

Reputation: 33945

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(user_id VARCHAR(12) NOT NULL PRIMARY KEY
,id_status CHAR(1) NOT NULL
,ip_binary CHAR(32) NOT NULL
,ip_network_length INT NOT NULL
);

INSERT INTO my_table VALUES
('800AA','A',10000011111001100100101011110010,32),
('800AB','A',10000011111001100100101000000000,24),
('800AC','X',10000011111001100000000000000000,16);


SELECT x.*
     , y.user_id
  FROM my_table x
  JOIN my_table y
    ON y.user_id <> x.user_id
   AND y.ip_binary LIKE CONCAT(SUBSTR(x.ip_binary,1,x.ip_network_length),'%')
 WHERE x.id_status <> 'x';

+---------+-----------+----------------------------------+-------------------+---------+
| user_id | id_status | ip_binary                        | ip_network_length | user_id |
+---------+-----------+----------------------------------+-------------------+---------+
| 800AB   | A         | 10000011111001100100101000000000 |                24 | 800AA   |
+---------+-----------+----------------------------------+-------------------+---------+

Upvotes: 1

Related Questions