2

Is there a way to get a parent and its children using a single query?

CREATE TABLE `servers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO servers (name) VALUES ('kvm01'), ('kvm02')

INSERT INTO servers (name, parent_id) VALUES ('web01', 1), ('db01', 1), ('web02', 2), ('db02', 2)

Current output:

mysql [lab]> SELECT child.id FROM servers AS parent LEFT JOIN servers AS child ON (child.parent_id = parent.id) WHERE parent.name = 'kvm01';
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

Expected output:

+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
+------+
4

You can either adjust your join condition

SELECT child.id
  FROM servers AS parent 
  LEFT JOIN servers AS child
    ON (child.parent_id = parent.id
     OR child.id = parent.id)
  WHERE parent.name = 'kvm01';

or use UNION / UNION ALL to retrieve the parent record separately:

SELECT child.id
  FROM servers AS parent 
  LEFT JOIN servers AS child
    ON child.parent_id = parent.id
  WHERE parent.name = 'kvm01'
UNION ALL
SELECT parent.id
  FROM servers AS parent
  WHERE parent.name = 'kvm01';
3
SELECT t2.*
FROM servers t1
LEFT JOIN servers t2 ON t1.id IN (t2.id, t2.parent_id)
WHERE t1.name = 'kvm01';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f41816149af8751cf109a4e1c0a249e

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.