Dear Community,
I've been busy with joining and concatenating, but I haven't found a solution yet.
Hopefully you can help me out.
1) connecting table "OrderVouchers"
OrderID OrderVoucherID Amount
1 1 10
2 2 20
3 3 20
36 36 12,5
36 37 12,5
37 38 15
2) Orders
| OrderID | transactionID | CustomerID | CompanyID | Orderdate | OrderTotal |
| 1 | 6 | 1 | 0 | 2-2-2014 | 10 |
| 2 | 9 | 2 | 0 | 3-2-2014 | 20 |
| 3 | 10 | 3 | 0 | 4-2-2014 | 20 |
| 36 | 34 | 2 | 0 | 12-2-2014 | 25 |
| 37 | 35 | 13 | 0 | 12-2-2014 | 15 |
3 ) Codes
| Codes | | | | | | |
| OrderVoucherID | CodeID | Codedate | Value | Code | Online | Additional |
| 1 | 1 | 0 | € 10,00 | x | 1 | 0 |
| 2 | 2 | 0 | € 20,00 | y | 1 | 0 |
| 3 | 3 | 0 | € 20,00 | z | 1 | 0 |
| 36 | 36 | 0 | € 12,50 | b | 1 | 0 |
| 37 | 37 | 0 | € 12,50 | c | 0 | 0 |
| 38 | 38 | 0 | € 15,00 | d | 1 | 1 |
The wanted output must be:
| OrderID | OrderVoucherID | CustomerID | Orderdate | Codedate | OrderTotal | Value | Code | Online | Additional |
| 1 | 1 | 1 | 2-2-2014 | 0 | 10 | € 10,00 | x | 1 | 0 |
| 2 | 2 | 2 | 3-2-2014 | 0 | 20 | € 20,00 | y | 1 | 0 |
| 3 | 3 | 3 | 4-2-2014 | 0 | 20 | € 20,00 | z | 1 | 0 |
| 36 | 36 | 2 | 12-2-2014 | 0 | 25 | € 12,50 | b | 1 | 0 |
| 36 | 37 | 2 | 12-2-2014 | 0 | 25 | € 12,50 | c | 0 | 0 |
| 37 | 38 | 13 | 12-2-2014 | 0 | 15 | € 15,00 | d | 1 | 1 |
So 1 order can have multiple vouchers.
It would be perfect to join the columns Orderdate and Codedate, because when one is filled with a date the other isn't, and visa versa.
Hope my question is clear. If there are any questions, let me know.
Kind regards,
Erwin
