da1lbi3 da1lbi3 - 5 months ago 6
SQL Question

Get results with like from 2 different tables and append them together

I want to get the debtors aditional adresss and with an specific id.

debtors

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


aditional_debtor

id debtors_id hnr place
1 1 12 Almelo


Result when I select on like debtors.name %companyname%

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


My query till now:

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


But how can I join the other table to get the aditional adresses and the "default" adress from the debtors table (The default adress must be the first row in the result set).

Answer

Use union and 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%'