Cfrgtkky

I have two tables: mother and child. Each mother has one or more children.

mother:

id | name  | age 

1  | name1 | 30

2  | name2 | 40 

3  | name3 | 35 

child:

id | name   | age | id_mother

1  | child1 | 15  | 1 

2  | child2 | 10  | 2

The result that I want is order mother order by age ASC and order by their children age asc

Edit (from comments): I simply want to order by minimum child age (if child exists), and then by mother age.

For example :

id | name  | age 

2  | name2 | 40 

1  | name1 | 30 

3  | name3 | 35

I tried this sql:

SELECT * 

FROM mother m

ORDER BY m.age asc , (SELECT MIN(age) FROM child c WHERE c.id_mother == m.id)

but it give just mothers ordered by their age?

I think you simply want to order by minimum child age (if child exists), and then by mother age.

• In a Correlated Subquery, you can get the minimum age out of the mother's children (if they exist).


• In ORDER BY, we will use IS NOT NULL to put the cases first in ordering where a minimum child age value exists.

Try:

SELECT

  m.*, 

  (SELECT MIN(c.age) 

   FROM child AS c

   WHERE c.id_mother = m.id) AS min_child_age 

FROM mother AS m 

ORDER BY 

  min_child_age IS NOT NULL DESC, 

  min_child_age ASC, 

  m.age ASC 

Schema (MySQL v5.7)

create table mother 

(id int, name varchar(8), age int);



insert into mother values

(1, 'name1', 30), (2, 'name2', 40), (3, 'name3', 35); 





create table child

(id int , name varchar(8), age int, id_mother int);



insert into child values 

(1  , 'child1' , 15  , 1 ),

(2  , 'child2' , 10  , 2);

Result

| id  | name  | age | min_child_age |

| --- | ----- | --- | ------------- |

| 2   | name2 | 40  | 10            |

| 1   | name1 | 30  | 15            |

| 3   | name3 | 35  |               |

View on DB Fiddle

try using this method :

select * from mother;

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

| id | name  | age |

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

|  1 | name1 |  30 |

|  2 | name2 |  40 |

|  3 | name3 |  35 |

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



select * from child;

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

| id | name   | age | id_mother |

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

|  1 | child1 |  15 |         1 |

|  2 | child2 |  10 |         2 |

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





SELECT mo1. * , (SELECT MIN( ch1.age ) FROM child AS ch1 WHERE ch1.id_mother = mo1.id) AS child_age FROM mother AS mo1 ORDER BY child_age IS NOT NULL DESC , child_age ASC ;

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

| id | name  | age | child_age |

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

|  2 | name2 |  40 |        10 |

|  1 | name1 |  30 |        15 |

|  3 | name3 |  35 |      NULL |

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