da1lbi3 da1lbi3 - 1 year ago 54
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.


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


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 Source

Use union and inner join

 select  d.hnr, d.place, d.name
 from debtors as d
 d.name LIKE '%companyname%'
 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%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download