da1lbi3 da1lbi3 - 4 months ago 9
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:

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

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

Aditional_debiteur
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.

Answer
SELECT
    o.id AS order_id
    ,d.name
    ,o.amount
    ,IFNULL(ad.hnr,d.hnr) AS hnr
FROM
    `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
WHERE
    o.debiteur_id = 1
    OR ad.id IS NOT NULL
ORDER BY
    o.id ASC

Here is one way to do it.

Comments