OlofL OlofL - 3 months ago 7
MySQL Question

MySQL join child + parent in same table and then join both with another table

I have two tables, the first with ipaddresses and ipsubnets. The table contains info such as ip, netmask and ownership. Every ip has a parent in the same table. The parent being the network address with netmask. 99,99% of the rows in this table has a parent.

The other table has ownership names which are connected to the ip addresses.

In my table most single ipaddressess (with netmask /32) have no ownership. The table is almost only populated with ownership when they are a subnet (less than /32 prefix).

The problem is that when I find no ownership on the row, I want to use the parents ownership. I have not been able to achive this yet, and I am not sure I am using the right techniques.

This is the create statement from the ipblock.

CREATE TABLE `ipblock` (
`address` DECIMAL(40,0) NOT NULL,
`description` VARCHAR(128) NULL DEFAULT NULL,
`first_seen` TIMESTAMP NOT NULL DEFAULT '1970-01-02 01:00:01',
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`info` TEXT NULL,
`interface` BIGINT(20) NULL DEFAULT NULL,
`last_seen` TIMESTAMP NOT NULL DEFAULT '1970-01-02 01:00:01',
`owner` BIGINT(20) NULL DEFAULT NULL,
`parent` BIGINT(20) NULL DEFAULT NULL,
`prefix` INT(11) NOT NULL,
`status` BIGINT(20) NOT NULL,
`used_by` BIGINT(20) NULL DEFAULT NULL,
`version` INT(11) NOT NULL,
`vlan` BIGINT(20) NULL DEFAULT NULL,
`use_network_broadcast` TINYINT(1) NOT NULL,
`monitored` TINYINT(1) NOT NULL,
`rir` VARCHAR(255) NULL DEFAULT NULL,
`asn` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ipblock1` (`address`, `prefix`),
INDEX `Ipblock2` (`parent`),
INDEX `Ipblock3` (`status`),
INDEX `Ipblock4` (`first_seen`),
INDEX `Ipblock5` (`last_seen`),
INDEX `Ipblock6` (`interface`),
INDEX `Ipblock7` (`vlan`),
INDEX `Ipblock8` (`version`),
INDEX `owner` (`owner`),
INDEX `used_by` (`used_by`),
INDEX `asn` (`asn`),
CONSTRAINT `fk_asn` FOREIGN KEY (`asn`) REFERENCES `asn` (`id`),
CONSTRAINT `fk_interface_3` FOREIGN KEY (`interface`) REFERENCES `interface` (`id`),
CONSTRAINT `fk_owner_2` FOREIGN KEY (`owner`) REFERENCES `entity` (`id`),
CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `ipblock` (`id`),
CONSTRAINT `fk_status_2` FOREIGN KEY (`status`) REFERENCES `ipblockstatus` (`id`),
CONSTRAINT `fk_used_by_1` FOREIGN KEY (`used_by`) REFERENCES `entity` (`id`),
CONSTRAINT `fk_vlan_1` FOREIGN KEY (`vlan`) REFERENCES `vlan` (`id`))
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=29404762
;


And this is from the entity table.

CREATE TABLE `entity` (
`acctnumber` VARCHAR(128) NULL DEFAULT NULL,
`aliases` VARCHAR(255) NULL DEFAULT NULL,
`asname` VARCHAR(32) NULL DEFAULT NULL,
`asnumber` INT(11) NULL DEFAULT NULL,
`availability` BIGINT(20) NULL DEFAULT NULL,
`config_type` VARCHAR(255) NULL DEFAULT NULL,
`contactlist` BIGINT(20) NULL DEFAULT NULL,
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`info` TEXT NULL,
`maint_contract` VARCHAR(128) NULL DEFAULT NULL,
`name` VARCHAR(128) NOT NULL,
`oid` VARCHAR(32) NULL DEFAULT NULL,
`short_name` VARCHAR(64) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `entity1` (`name`),
UNIQUE INDEX `entity2` (`oid`),
INDEX `Entity3` (`asname`),
INDEX `Entity4` (`asnumber`),
INDEX `availability` (`availability`),
INDEX `contactlist` (`contactlist`),
CONSTRAINT `fk_availability` FOREIGN KEY (`availability`) REFERENCES `availability` (`id`),
CONSTRAINT `fk_contactlist_2` FOREIGN KEY (`contactlist`) REFERENCES `contactlist` (`id`))
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=146
;


This is the query I've been playing around with:

SELECT
INET_NTOA(ipblock.address) AS "Address",
ipblock.prefix AS "Netmask",
IF (ipblock.used_by IS NULL,
parentNAME.name,
entity.name) AS "Used by"

FROM ipblock

LEFT JOIN ipblock AS parentIP
ON ipblock.parent = parentIP.id

LEFT JOIN entity
ON ipblock.used_by = entity.id
#AND parentIP.used_by = entity.id

LEFT JOIN entity AS parentNAME
ON parentIP.used_by = entity.id

group by ipblock.id, parentIP.id
#order by parentIP.id


The first LEFT JOIN I expect to make an alias of the ipblock table named parentIP. Since every ipblock. row has a parent, I want ipblock.parent be parentIP.id. This seem to work as expected.

The second LEFT JOIN is to get the used_by field to connect to the entity.id. This also works as expected and I can select the entity.name from the ones who has a ownership.

The third LEFT JOIN i try to connect the earlier created alias parentIP.used_by to the entity.id again. When i use my IF statement in the select to output parentNAME.name, i don't get expected result. Only entity.name works.

Am I using the wrong techniques here?

| Address | Netmask | Used by |
| 172.18.5.0 | 24 | Network Services |
| 172.18.5.7 | 32 | NULL |
| 172.18.5.24 | 32 | NULL |
| 172.16.40.0 | 24 | company-xyz |
| 172.16.41.0 | 24 | company-xyz |
| 172.18.30.0 | 24 | Network Services |
| 172.18.30.2 | 32 | NULL |
| 172.18.30.3 | 32 | NULL |
| 172.24.0.40 | 29 | company-xyz |
| 172.18.5.25 | 32 | Network Services |
| 172.16.80.0 | 24 | 1234-customer |
| 172.16.80.2 | 32 | 1234-customer |
| 172.24.0.24 | 29 | 1234-customer |
| 10.0.1.0 | 24 | NULL |
| 172.24.0.0 | 29 | internal-service2 |
| 172.18.5.15 | 32 | Network Services |
| 172.18.222.224 | 29 | NULL |
| 172.18.222.248 | 32 | NULL |
| 172.18.222.188 | 32 | NULL |


Some more sample columns from the database to get a better overview.

```sql
SELECT
INET_NTOA(ipblock.address),
ipblock.prefix,
ipblock.id,
ipblock.parent AS "parent id",
ipblock.used_by AS "entity id",
entity.name,
ipblock.id
FROM ipblock
LEFT JOIN entity
ON ipblock.used_by = entity.id

```
| INET_NTOA(ipblock.address) | prefix | id | parent id | entity id | name | id |
| - | -: | -: | -: | -: | - | -: |
| NULL | 10 | 6 | NULL | 1 | Unknown | 6 |
| 172.18.5.0 | 24 | 15 | 29343867 | 24 | Network Services | 15 |
| 172.18.5.7 | 32 | 20 | 15 | NULL | NULL | 20 |
| 172.18.5.24 | 32 | 38 | 15 | NULL | NULL | 38 |
| 172.16.40.0 | 24 | 40 | 25389918 | 68 | customer-abc | 40 |
| 172.16.41.0 | 24 | 46 | 25389918 | 68 | customer-abc | 46 |
| 172.18.30.0 | 24 | 52 | 29343867 | 24 | Network Services | 52 |
| 172.18.30.2 | 32 | 54 | 52 | NULL | NULL | 54 |
| 172.18.30.3 | 32 | 55 | 52 | NULL | NULL | 55 |
| 172.24.0.40 | 29 | 58 | 27082404 | 68 | customer-abc | 58 |
| 172.18.5.25 | 32 | 64 | 15 | 24 | Network Services | 64 |
| 172.16.80.0 | 24 | 66 | 25389918 | 131 | cust | 66 |
| 172.16.80.2 | 32 | 68 | 66 | 131 | cust | 68 |
| 172.24.0.24 | 29 | 72 | 27082404 | 131 | cust | 72 |
| 10.0.1.0 | 24 | 79 | 3 | NULL | NULL | 79 |
| 172.24.0.0 | 29 | 85 | 27082404 | 73 | xyz-customer | 85 |
| 172.18.5.15 | 32 | 91 | 15 | 24 | Network Services | 91 |


I'm new to SQL, so please explain to me like I'm 5.

Answer

I believe you got the 3rd join wrong. The 3rd join in your query is as follows:

LEFT JOIN entity AS parentNAME
ON parentIP.used_by = entity.id

You are not joining to the parentName alias in the on clause, but to the base entity alias. It should be

LEFT JOIN entity AS parentNAME
ON parentIP.used_by = parentNAME.id

In this case, I do not think that you would need the group by clause either. I would also change the way you calculate the used by expression:

coalesce(entity.name, parentNAME.name) AS "Used by"

Coalesce() function returns the 1st non-null value in its parameter list, or null, if no non-null value is provided as parameter.