mysql - 从 2 个不同的表中获取类似的结果并将它们附加在一起

Question

我想获得具有特定 ID 的债务人的附加地址。

债务人

id hnr place      name
1  22  Amsterdam  companyname
2  26  Enschede   anotherCompany

aditional_debtor

id debtors_id hnr place 
1  1          12  Almelo

当我选择 like debtors.name %companyname% 时的结果

hnr debtors.id  place       name
22  1           Amsterdam   companyname
12  1           Almelo      companyname

到目前为止我的查询:

SELECT * FROM debtors d where d.name LIKE '%companyname%'; 

但是我怎样才能加入另一个表来从债务人表中获取额外的地址和“默认”地址(默认地址必须是结果集中的第一行)。

Answer 1

使用union和inner join

 select  d.hnr, d.place, d.name
 from debtors as d
 d.name LIKE '%companyname%'
 union 
 select  a.hnr, a.place, c.name
 from aditional_debtor as a 
 inner join debtors as c on a.debtors_id = c.id
 and  c.name LIKE '%companyname%'