SQL self join result of a select

Question

I have two tables, lets call them A and B which I perform an inner join on.

select
A.id, 
A.serial_number as serial, 
concat(B.type, '-', B.primary, '-', B.secondary) as product_number, A.parent_id as parent
from A
inner join B on A.number_id = B.id) as T1

as a result I get a set that contains parents and children (1 per parent).

+----+--------+-----------------+--------+
| id | serial | product number  | parent |
+----+--------+-----------------+--------+
| 1  |    123 | abc             | null   |
| 2  |    234 | cde             | 1      |
| 3  |    456 | abc             | null   |
| 4  |    895 | cde             | 2      |
+----+--------+-----------------+--------+

now I'd like to do a self join to get the following

+----+---------------+------------------------+---------------+-----------------------+
| id | serial parent | product_number parent  | serial child  | product_number child  |
+----+---------------+------------------------+---------------+-----------------------+
| 1  |           123 | abc                    |           234 | cde                   |
| 2  |           456 | abc                    |           895 | cde                   |
+----+---------------+------------------------+---------------+-----------------------+

What would be the best approach for this, I simply couldn't find an easy solution... is there a way to join T1 with itself?

Answer 1

I think that's more joins:

select
    ap.id as id_parent, 
    ap.serial_number as serial_parent, 
    concat_ws('-', bp.type, bp.primary, bp.secondary) as product_number_parent, 
    ac.child as id_child,
    ac.serial_number as serial_child,
    concat_ws('-', bc.type, bc.primary, bc.secondary) as product_number_child
from a ap 
inner join a ac on ac.parent = ap.id
inner join b bp on bp.id = ap.astrol_number_id
inner join b bc on bc.id = ac.astrol_number_id
where ap.parent is null