Map values to column in sql-CodePudding

I have two tables. Table A has details of Customer and Order and Table B has details of Order and values and I want my final result with combination of all the details. As order date is common field in both the columns and we have duplicate order date values mapped to order id, how can I achieve that in plsql?

Table A

CustomerID OrderID OrderDt
---------- ------- ------
123        76542   01APR
123        77923   01APR
123        78542   02APR
456        77654   02APR
789        76890   03APR

Table B

OrderDt OrderValue
------- ----------
01APR   760
01APR   540
02APR   154
02APR   228
03APR   990

Final Result

CustomerID OrderID OrderDt OrderVal
---------- ------- ------- --------
123        76542   01APR   760
123        77923   01APR   540
123        78542   02APR   154
456        77654   02APR   228
789        76890   03APR   990

CodePudding user response：

There is no way to do this and guarantee the correct result. Your Table B needs a foreign key referencing Table A OrderID. You shouldn't try to fix a broken data model with a SQL hack.

But if you want to try anyway...

with taba as (
  select a.*
         ,row_number() over (partition by a.orderdt order by a.orderid) as rn
  from a)
 , tabb as (
  select b.*
         ,row_number() over (partition by b.orderdt order by b.orderval) as rn
  from b)
select  taba.customerid
       ,taba.orderid
       ,taba.orderdt
       ,tabb.orderval
from taba
join tabb
  on  taba.orderdt = tabb.orderdt
  and taba.rn.     = tabb.orderdt
/

This guarantees a consistent result set, but not necessarily a correct one, because part of the join criteria is determined by sorting on orderval, which is clearly nonsense. As I said, we can't fix a broken data model with SQL.

Now if your table B has some other column which you haven't included in this example and that column implies a creation order for records then by all means use that column in the tabb subquery row_number() ordering clause.

But the only correct solution is to add ORDERID to table B.