Get same result with join from other row

Question

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 1

Check this http://sqlfiddle.com/#!9/cb12e8/11 :

SELECT `o`.`order_nr`, 
CASE WHEN `ds2`.`id` IS NOT NULL THEN `ds2`.`Cd_deb` ELSE `ds1`.`Cd_deb` END as cd_deb, 
CASE WHEN `ds2`.`id` IS NOT NULL THEN `dsa2`.`Name_org` ELSE `ds1`.`Name_org` END as `Name_org`,
CASE WHEN `ds2`.`id` IS NOT NULL THEN `dsa2`.`Hnr` ELSE `ds1`.`Hnr` END as `Hnr`,
`oc`.`backorder`
FROM `orders` `o` 
LEFT JOIN `orders_cart` `oc` ON `o`.`id` = `oc`.`orders_id` 
LEFT JOIN `deb_stam` `ds1` ON `ds1`.`id` = `o`.`deb_id`
LEFT JOIN `deb_stam_address` `dsa1` ON `dsa1`.`deb_stam_id`=`ds1`.`id`
LEFT JOIN `deb_stam_address` `dsa2` ON `dsa2`.`id` = `o`.`aditional_deb_id`
LEFT JOIN `deb_stam` `ds2` ON `dsa2`.`deb_stam_id`=`ds2`.`id`
WHERE `oc`.`backorder` > 0 
GROUP BY `oc`.`id`

Answer 2

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.

The following sql will work if you want to join through orders.aditional_deb_id = deb_stam.id (which makes more sense from a design perspective):

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

However if you want to join through orders.aditional_deb_id = deb_stam_address.id then this sql would work:

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
  left join deb_stam_address dsa on dsa.id = o.aditional_deb_id
  left join deb_stam ds on dsa.deb_stam_id = ds.id
  where oc.backorder > 0
) r
order by r.order_nr

test sql1:

mysql> drop table if exists orders;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table if exists deb_stam;
Query OK, 0 rows affected (0.10 sec)

mysql> drop table if exists deb_stam_address;
Query OK, 0 rows affected (0.10 sec)

mysql> drop table if exists orders_cart;
Query OK, 0 rows affected (0.09 sec)

mysql> create table orders (
    -> id int(11),
    -> order_nr int(11),
    -> deb_id int(11),
    -> aditional_deb_id int(11)
    -> );
Query OK, 0 rows affected (0.59 sec)

mysql> insert into orders (id, order_nr, deb_id, aditional_deb_id)
    -> values (1, 199998, 1, null), (2, 199999, null, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table deb_stam (
    -> id int(11),
    -> Cd_deb varchar(10),
    -> Name_org varchar(10),
    -> Hnr int(11)
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> insert into deb_stam(id, Cd_deb, Name_org, Hnr)
    -> values (1, "E23", "Apple", 12), (2, "r25", "Philips", 9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table deb_stam_address (
    -> id int(11),
    -> deb_stam_id int(11),
    -> Name_org varchar(10),
    -> Hnr int(11)
    -> );
Query OK, 0 rows affected (0.69 sec)

mysql> insert into deb_stam_address (id, deb_stam_id, Name_org, Hnr)
    -> values (1, 2 , "Phil", 222);
Query OK, 1 row affected (0.01 sec)

mysql> create table orders_cart (
    -> id int(11),
    -> orders_id int(11),
    -> supplier int(11),
    -> backorder int(11)
    -> );
Query OK, 0 rows affected (0.55 sec)

mysql> insert into orders_cart(id, orders_id, supplier, backorder)
    -> values (1, 1, 5, 2),(2, 2, 2, 1),(3, 2, 3, 0);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 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;
+----------+--------+----------+------+-----------+
| order_nr | Cd_deb | Name_org | Hnr  | backorder |
+----------+--------+----------+------+-----------+
|   199998 | E23    | Apple    |   12 |         2 |
|   199999 | r25    | Phil     |  222 |         1 |
+----------+--------+----------+------+-----------+
2 rows in set (0.00 sec)

test sql2:

mysql> drop table if exists orders;
Query OK, 0 rows affected (0.14 sec)

mysql> drop table if exists deb_stam;
Query OK, 0 rows affected (0.17 sec)

mysql> drop table if exists deb_stam_address;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table if exists orders_cart;
Query OK, 0 rows affected (0.11 sec)

mysql> create table orders (
    -> id int(11),
    -> order_nr int(11),
    -> deb_id int(11),
    -> aditional_deb_id int(11)
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> insert into orders (id, order_nr, deb_id, aditional_deb_id)
    -> values (1, 199998, 1, null), (2, 199999, null, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table deb_stam (
    -> id int(11),
    -> Cd_deb varchar(10),
    -> Name_org varchar(10),
    -> Hnr int(11)
    -> );
Query OK, 0 rows affected (0.67 sec)

mysql> insert into deb_stam(id, Cd_deb, Name_org, Hnr)
    -> values (1, "E23", "Apple", 12), (2, "r25", "Philips", 9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table deb_stam_address (
    -> id int(11),
    -> deb_stam_id int(11),
    -> Name_org varchar(10),
    -> Hnr int(11)
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql> insert into deb_stam_address (id, deb_stam_id, Name_org, Hnr)
    -> values (1, 2 , "Phil", 222);
Query OK, 1 row affected (0.01 sec)

mysql> create table orders_cart (
    -> id int(11),
    -> orders_id int(11),
    -> supplier int(11),
    -> backorder int(11)
    -> );
Query OK, 0 rows affected (1.69 sec)

mysql> insert into orders_cart(id, orders_id, supplier, backorder)
    -> values (1, 1, 5, 2),(2, 2, 2, 1),(3, 2, 3, 0);
Query OK, 3 rows affected (0.23 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 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
    ->   left join deb_stam_address dsa on dsa.id = o.aditional_deb_id
    ->   left join deb_stam ds on dsa.deb_stam_id = ds.id
    ->   where oc.backorder > 0
    -> ) r
    -> order by r.order_nr;
+----------+--------+----------+------+-----------+
| order_nr | Cd_deb | Name_org | Hnr  | backorder |
+----------+--------+----------+------+-----------+
|   199998 | E23    | Apple    |   12 |         2 |
|   199999 | r25    | Phil     |  222 |         1 |
+----------+--------+----------+------+-----------+
2 rows in set (0.00 sec)