Join a Tree Based Table-CodePudding

I have a fact table "Pricing" like this:

Serial	Main_Category	Group_Category	Sub_Category	Item	Location	Price
1	Clothes	Men	T Shirts	T Shirt 1	Store 1	100
2	Clothes	Men	T Shirts	T Shirt 1	All	25
3	Devices	TVs	Smart TVs	SmartTV 1	Store 1	50
4	Devices	TVs	Smart TVs	All	Store 1	75

You can note that not all items are explicitly defined, if the item is not mentioned, it should be available in the "All".

"All" could be found in any column except for Price

Another dimension table "Sales" like this:

Serial	Main_Category	Group_Category	Sub_Category	Item	Location
1	Clothes	Men	T Shirts	T Shirt 1	Store 1
2	Clothes	Men	T Shirts	T Shirt 1	Store 3
3	Devices	TVs	Smart TVs	SmartTV 3	Store 1
4	Devices	TVs	Smart TVs	SmartTV 1	Store 1

I would like to add the price from the Pricing table to the Sales table without using a join for each combination

Excepted output:

Serial	Main_Category	Group_Category	Sub_Category	Item	Location	Price
1	Clothes	Men	T Shirts	T Shirt 1	Store 1	100
2	Clothes	Men	T Shirts	T Shirt 1	Store 3	25
3	Devices	TVs	Smart TVs	SmartTV 3	Store 1	75
4	Devices	TVs	Smart TVs	SmartTV 1	Store 1	50

Update: "All" could be found in more than one column

CodePudding user response：

You can do

select *
from (
    select s.*, p.Price, row_number() over(partition by s.Main_Category, .., s.Location order by p.Serial) rn
    from Sales s
    join Pricing p on 
      (s.Main_Category = p.Main_Category or p.Main_Category ='All')
      and ..
      and (s.Location = p.Location or p.Location ='All')
 ) t
 where rn = 1;