Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 5

Server version: 10.0.8-MariaDB-log Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test;

Database changed

MariaDB [test]> CREATE TABLE t (id bigint(20), name varchar(30),

parent bigint(20), primary key (id)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.27 sec)

MariaDB [test]> INSERT INTO t values (1, 'a', NULL), (4, 'b', 1), (5,

'c', 4), (10, 'd', 1);

Query OK, 4 rows affected (0.04 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT

OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a') GROUP BY

t1.id;

+----+------+------+------+

| id | name | id   | name |

+----+------+------+------+

|  1 | a    | NULL | NULL |

|  4 | b    | NULL | NULL |

|  5 | c    | NULL | NULL |

| 10 | d    | NULL | NULL |

+----+------+------+------+

4 rows in set (0.00 sec)

MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT

OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a');

+----+------+------+------+

| id | name | id   | name |

+----+------+------+------+

|  1 | a    | NULL | NULL |

|  4 | b    | NULL | NULL |

|  5 | c    |    4 | b    |

| 10 | d    | NULL | NULL |

+----+------+------+------+

4 rows in set (0.00 sec)

MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT

OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a') GROUP BY

t1.id, t1.name;

+----+------+------+------+

| id | name | id   | name |

+----+------+------+------+

|  1 | a    | NULL | NULL |

|  4 | b    | NULL | NULL |

|  5 | c    |    4 | b    |

| 10 | d    | NULL | NULL |

+----+------+------+------+

4 rows in set (0.00 sec)

MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT

OUTER JOIN t t2 ON (t1.parent = t2.id) GROUP BY t1.id;

+----+------+------+------+

| id | name | id   | name |

+----+------+------+------+

|  1 | a    | NULL | NULL |

|  4 | b    |    1 | a    |

|  5 | c    |    4 | b    |

| 10 | d    |    1 | a    |

+----+------+------+------+

4 rows in set (0.00 sec)