da1lbi3 da1lbi3 - 3 months ago 5x
MySQL Question

Select from same table and via subtable to one result set

This setup is for multiple addresses for one debtor. I have 2 fields for the debtor in my order table. debiteur_id for the "default" value from the debtor table. If we choose another addres for the same debtor the debiteur_id is null and aditional_id is filled in with the value from aditional_debiteur wich is linked to debtor. I hope someone gets it.

My tables:

id debiteur_id amount aditional_id
1 null 2 1
2 1 3 null
3 2 1 null

id name hnr place
1 myname 10 london
2 companyName 4 Enschede

id debiteur_id hnr place
1 1 22 amsterdam

What I want is to get all the orders linked (Direct or inderict) to an id from the debtor table. This because the order is from the same company only with another adress.
If I choose debor with id 1 I get this order results:

order_id name amount
1 myname 2
2 myname 3

Another result with debtor id 2:

order_id name amount
3 companyName 1

I have this query:

SELECT * FROM `Order` WHERE `debiteur_id` = '1' ORDER BY `id` ASC

This returns the result with the default debtor adress, I want to retrieve the other orders with the aditional_id (wich are linked to the debtor with the debituer_id) also.

Long story short. I want to retrieve all orders from the order table with an specific debtor from the debtor table.

    o.id AS order_id
    ,IFNULL(ad.hnr,d.hnr) AS hnr
    `Order` o
    LEFT JOIN Additional_debiteur ad
    ON o.aditional_id = ad.id
    AND ad.debiteur_id = 1
    INNER JOIN debtor d
    ON IFNULL(o.debiteur_id,ad.debiteur_id) = d.id
    o.debiteur_id = 1
    OR ad.id IS NOT NULL
    o.id ASC

Here is one way to do it.