mysql - 如何合并两个表，其中一个是另一个的父表？

Question

我正在尝试将两个表合并在一起并维护一个表是另一个表的子表的事实。我想表明它有一个父级，同时仍然保持父级具有其独特的值。

这是我的关系:subdomain.domain_id 与(你猜对了)domain.id 相关

我的表格示例

域名:

id | name | description | validator | need | need_amount
---|------|-------------|-----------|------|-------------
5  | prog | this iss..  | 6         | low  | 5

子域

id | name | description | validator | domain_id | need | need_amount
---|------|-------------|-----------|-----------|------|------------
5  | java | java is...  | 10        | 5         | high | 10
6  | c++  | c++...      | 9         | 5         | high | 10

我希望能够看到

id | domain_name | subdomain_name | description | validator | need | need_amount
---|-------------|----------------|-------------|-----------|------|-------------
5  | prog        | null           | this iss..  | 6         | low  | 5
5  | prog        | java           | this is...  | 10        | high | 10
6  | prog        | c++            | this is..   | 9         | high | 10

或者像这样，(最好是上面的那个)

id | name         | description | validator | need | need_amount
---|--------------|-------------|-----------|------|------------
5  | prog         | this iss..  | 6         | low  | 5
5  | prog - java  | java is...  | 10        | high | 10
6  | prog - c++   | c++...      | 9         | high | 10

有什么想法吗？？？是否可以？谢谢!

感谢@Pradeep

SELECT id,
       name AS domain_name,
       NULL AS subdomain_name,
       description,
       validator,
       need,
       need_amount
FROM   domain 
UNION ALL
SELECT a.id,
       b.name AS domain_name,
       a.name            AS subdomain_name,
       a.description,
       a.validator,
       a.need,
       a.need_amount
FROM   subdomain a join domain b on a.domain_id=b.id 

Answer 1

试试这个。

SELECT id,
       name AS domain_name,
       NULL AS subdomain_name,
       description,
       validator,
       need,
       need_amount
FROM   parent 
UNION ALL
SELECT a.id,
       b.domain_name AS domain_name,
       a.name            AS subdomain_name,
       a.description,
       a.validator,
       a.need,
       a.need_amount
FROM   child a join parent b on a.domain_id=b.domain_id