Get a parent and the children using the parent name

Question

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 |
+------+

Answer 1

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';

Answer 2

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