I have the following basic 3 Table Structure in mariadb/mysql.
x
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 dataset to avoid unnecessary clutter.
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.orgMy guess is, it has something to do with the MAX() Function has troubles with mixed Content in the Value Column. When leaving out 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 | NULLThis Query gives 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/ |+---------+-------------+------------------------------------------------------------------+Advertisement
Answer
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.org172.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.