da1lbi3 da1lbi3 - 5 months ago 10
SQL Question

Get same result with join from other row

I want to receive the exact same debtor information from a different field when the other is null. My tables look like this:

orders
id order_nr deb_id aditional_deb_id
1 199998 1 null
2 199999 null 1

deb_stam
id Cd_deb Name_org Hnr
1 E23 Apple 12
2 r25 Philips 9

deb_stam_address
id deb_stam_id Name_org Hnr
1 2 Phil 222

orders_cart
id orders_id supplier backorder item_nr
1 1 5 2 not for now
2 2 2 1 not for now
3 2 3 0 not for now


Expected result:

order_nr Cd_deb Name_org Hnr backorder
199998 E23 Apple 12 2
199999 r25 Phil 222 1


Orders_cart is the table where the rows for order products are in. What I want is to receive all the orders where the backorder is bigger than 0. That is not an issue. But I want the debtor in the result set. The deb_stam is the "default" adres for an customer, when the shipment is sent to another adres from the same customer, the id is linked to aditional_deb_id (deb_stam_adress) on the order. And you get the base information such as Cd_deb with the deb_stam_id in that table.

I Have this query till now. But how can I handle the debtors issue?

SELECT `o`.`order_nr`, `oc`.`backorder`
FROM `orders` `o`
LEFT JOIN `orders_cart` `oc` ON `o`.`id` = `oc`.`orders_id`
WHERE `oc`.`backorder` > 0
GROUP BY `oc`.`id`

Answer

Union the two data sets together. The below SQL assumes that you only want records from the orders_cart where a join exists to the orders and deb_stam / deb_stam_address tables.

select r.*
from (
  select o.order_nr, ds.Cd_deb, ds.Name_org, ds.Hnr, oc.backorder
  from orders_cart oc 
  inner join orders o on o.id = oc.orders_id 
  inner join deb_stam ds on o.deb_id = ds.id
  where oc.backorder > 0
  union
  select o.order_nr, ds.Cd_deb, dsa.Name_org, dsa.Hnr, oc.backorder
  from orders_cart oc 
  inner join orders o on o.id = oc.orders_id and o.deb_id is null
  inner join deb_stam ds on o.aditional_deb_id = ds.id
  left join deb_stam_address dsa on dsa.deb_stam_id = ds.id
  where oc.backorder > 0
) r
order by r.order_nr