I am trying to combine the data from two tables sales & purchase in one table. Both Sales & Purchase table shares foreign key from third table store.
What is the best way to construct the query to get the results exactly as in results tables. I feel that that the example below is the one most common for any DB user. But I am finding it very difficult to get the expected result.
I have tried select with union but not getting the result as expected.
Please also advise if there is any design flaw in creating the DB design.
The source tables and expected query result table is shown in the image.