CODEWITHSUNDEEP
mysqldatabase-design
user1506630
user1506630

Reputation: 405

Selecting grandchildren from parents table, dependant on child's type

I have the following five tables:

       Parents:          
    +--+---------+   
    |id|   name  |   
    +--+---------+   
    | 1|  lorem  |
    | 2|  ipsum  |   
    | 3|  lorem  |   
    +--+---------+

    Daughters:                                 Sons:
+--+------+-------+                      +--+------+-------+
|id|parent|  name |                      |id|parent|  name |
+--+------+-------+                      +--+------+-------+
| 1|   3  | lorem |                      | 1|   1  | lorem |
| 2|   1  | ipsum |                      | 2|   3  | ipsum |
| 3|   2  | lorem |                      | 3|   1  | lorem |
+--+------+-------+                      +--+------+-------+


    GrandDaughters:                          GrandSons:
+--+------+-------+                      +--+------+-------+
|id|parent|  name |                      |id|parent|  name |
+--+------+-------+                      +--+------+-------+
| 1|   3  | lorem |                      | 1|   1  | lorem |
| 2|   1  | ipsum |                      | 2|   3  | ipsum |
| 3|   1  | lorem |                      | 3|   2  | lorem |
+--+------+-------+                      +--+------+-------+

I am trying to select from the above five tables, so that i get a result similar to this: 

Result
+------+-------------+-------------------------------+
| Name | Child's name| GrandSons/GrandDaughters name |
+------+-------------+-------------------------------+
| lorem|    ipsum    |                lorem          |
| lorem|    lorem    |                ipsum          |
| ipsum|    lorem    |                lorem          |
+------+-------------+-------------------------------+

Note that, I presume only Daughters can have GrandDaughters and only Sons can have GrandSons. Depending on whether the Parent's child is a Son or a Daughter, I would need to have the third column to be respectively either be GrandSon or GrandDaughter. Is this doable without making more than a one query?

Upvotes: 0

Views: 112

Answers (1)

Jens
Jens

Reputation: 69450

Try tis:

select p.name, d.name, g.name from parent p join daughters d on p.id = d.parent join granddaughters g on d.id=g.parent
union all
select p.name, s.name, g.name from parent p join sons s on p.id = s.parent join grandsuns g on s.id=g.parent

Upvotes: 1

Related Questions