I have the following basic 3 Table Structure in mariadb/mysql.
MariaDB [aix_registry]> describe nodes;
------- -------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------- -------------- ------ ----- --------- ----------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(256) | NO | | NULL | |
------- -------------- ------ ----- --------- ----------------
2 rows in set (0.036 sec)
MariaDB [aix_registry]> describe attribs;
------------ -------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------ -------------- ------ ----- --------- ----------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(256) | NO | | NULL | |
| persistent | int(11) | YES | | 0 | |
| parent | varchar(256) | YES | | NODE | |
------------ -------------- ------ ----- --------- ----------------
4 rows in set (0.042 sec)
MariaDB [aix_registry]> describe entries;
----------- -------------- ------ ----- --------------------- ----------------
| Field | Type | Null | Key | Default | Extra |
----------- -------------- ------ ----- --------------------- ----------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| node_id | int(11) | NO | MUL | NULL | |
| attrib_id | int(11) | NO | MUL | NULL | |
| value | varchar(256) | NO | | NULL | |
| ts | timestamp | NO | | current_timestamp() | |
----------- -------------- ------ ----- --------------------- ----------------
5 rows in set (0.052 sec)
This simple SELECT returns incomplete records. I reduced the output of all follwing examples to a single set.
SELECT nodes.id AS NODE_ID, nodes.name AS NODE ,
MAX(CASE WHEN attribs.name = 'IP_LONG' THEN value END) AS IP_LONG,
MAX(CASE WHEN attribs.name = 'IP' THEN value END) AS IP,
MAX(CASE WHEN attribs.name = 'LOCATION' THEN value END) AS LOCATION
from entries left join nodes on nodes.id = node_id left join attribs on attribs.id = attrib_id WHERE entries.ts > DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY nodes.name ORDER BY nodes.id ;
--------- ------------- ------------------------------------------- -------------- ------------
| NODE_ID | NODE | IP_LONG | IP | LOCATION |
--------- ------------- ------------------------------------------- -------------- ------------
| 31 | AIXDX4-TEST | 172.17.9.196/255.255.248.0/172.17.15.255/ | 172.17.9.196 | Wienerberg |
--------- ------------- ------------------------------------------- -------------- ------------
the IP_LONG column is missing the follwing for example...
172.16.84.74/255.255.192.0/172.16.127.255/aixdx4-test.domain.org
my guess is, it has something to do with the MAX() function has troubles with mixed content in the value column. when omitting MAX() and GROUP BY the missing values are shown but output is kind of chaotic.
SELECT nodes.id AS NODE_ID, nodes.name AS NODE,
CASE WHEN attribs.name = 'IP_LONG' THEN value END AS IP_LONG,
CASE WHEN attribs.name = 'IP' THEN value END AS IP,
CASE WHEN attribs.name = 'LOCATION' THEN value END AS LOCATION
from entries left join nodes on nodes.id = node_id left join attribs on attribs.id = attrib_id
WHERE entries.ts > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY nodes.id;
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | 172.17.9.196 | NULL |
| 31 | AIXDX4-TEST | 172.17.9.196/255.255.248.0/172.17.15.255/ | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | 172.16.84.74 | NULL |
| 31 | AIXDX4-TEST | 172.16.84.74/255.255.192.0/172.16.127.255/aixdx4-test.domain.org | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | 172.16.13.196 | NULL |
| 31 | AIXDX4-TEST | 172.16.13.196/255.255.254.0/172.16.13.255/ | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | Wienerberg |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL | NULL | NULL |
| 31 | AIXDX4-TEST | NULL
this query give the right output, but i am unclear how to integrate that in the above, but this is a topic for another question.
SELECT nodes.id AS NODE_ID, nodes.name AS NODE, entries.value AS IP_NETMASK_BROADCAST_DNS FROM (entries LEFT JOIN nodes ON(nodes.id = entries.node_id)) WHERE entries.attrib_id = (SELECT attribs.id FROM attribs WHERE attribs.name = 'IP_LONG') and cast(entries.ts as date) = curdate() and nodes.id = '31' ORDER BY nodes.name;
--------- ------------- ------------------------------------------------------------------
| NODE_ID | NODE | IP_NETMASK_BROADCAST_DNS |
--------- ------------- ------------------------------------------------------------------
| 31 | AIXDX4-TEST | 172.17.9.196/255.255.248.0/172.17.15.255/ |
| 31 | AIXDX4-TEST | 172.16.84.74/255.255.192.0/172.16.127.255/aixdx4-test.domain.org |
| 31 | AIXDX4-TEST | 172.16.13.196/255.255.254.0/172.16.13.255/ |
--------- ------------- ------------------------------------------------------------------
any ideas?
CodePudding user response：
These are the 3 values you are getting a max() of
172.17.9.196/255.255.248.0/172.17.15.255/
172.16.84.74/255.255.192.0/172.16.127.255/aixdx4-test.domain.org
172.16.13.196/255.255.254.0/172.16.13.255/
These are strings -- max looks at a "alphabetical" max. Since the "9" is greater than the 8 and the 1 the first one 172.17.9.196/255.255.248.0/172.17.15.255/ is picked. These values are all different -- which do you want and why? Do you want the longest one? The longest one would require different code.