I have several SQL tables I'm working with, and I want to join each other. I can perform join operations and see the tables under one roof. But there is an error in the join process I did, I need to sort and select the last one at the same time while joining. However, I couldn't do that If I had to explain with an example;
I have the following tables;
Columns: StoreCode, ProductCode, Stock, Time
Rule: This is my main table, we must join other tables here. StoreCode+ProductCode will be used as the key.
Columns: TransferNo, StoreCode, ProductCode, OnTheWayStock, Status, Time
Rule: There is more than one status for 1 transfer here. The current status of the transfer is written in the table according to the process. The number of stocks is taken according to the status. "Pending" if the status of the transfer is S1, S2; if the status of the transfer is S3, S4, it is called "OnTheWay". Here, it is only necessary to get the current status number and write it under "Waiting" or "OnTheWay" to sort the statuses and get the current one. So, if a transfer's final status is S4, it will take this number and write it under "OnTheWay", and under "Waiting" if S2. For a product from different transfers, "Waiting" and "OnTheWay" may come together. I use StoreCode+ProductCode as the key.
Columns: StoreCode, ProductCode, OpenOrder, Status
Rule: I join the open order quantities for a store in this table into my first table. Here, I remove the statuses "Cancel" and "Done". I use StoreCode+ProductCode as the key.
The most complicated situation here is the correct numbers of Table2 to Table1. I couldn't make it here. Is there anyone who can help? Thank you in advance