how to get column from tables in priority

I have three table that has priority for select column in SELECT statement.

Suppose table A has some columns like:

Table A:

A_ID     |name
---------|-------
1        |name1

Table B:

purchase |A_ID |type | market | group | rate    |   max  |   min
---------|-----|-----|--------|-------|---------|--------|---------
    1    |  1  |  1  |   1    |   1   |  0.12   |  1000  |   500
    1    |  1  |  2  |   1    |   1   |  0.3    |  2000  |   1500
    0    |  1  |  3  |   1    |   1   |  0.2    |  5000  |   800
    0    |  1  |  4  |   1    |   1   |  0.6    |  8000  |   2800
    0    |  1  |  6  |   1    |   1   |  0.7    |  null  |   2800

Table C:

purchase |A_ID |type | market | group | rate    |   max  |   min
---------|-----|-----|--------|-------|---------|--------|---------
    1    |  1  |  1  |   1    | null  |  0.2    |  null  |   null
    1    |  1  |  2  |   1    | null  |  null   |  5000  |   3000
    0    |  1  |  3  |   1    | null  |  0.5    |  3000  |   1000
    0    |  1  |  5  |   1    | null  |  0.4    |  3800  |   2000
    0    |  1  |  6  |   1    | null  |  null   |  null  |   3000

Desired result:

purchase |A_ID |type | market | rate    |   max  |   min
---------|-----|-----|--------|---------|--------|---------
    1    |  1  |  1  |   1    |  0.2    |  1000  |   500
    1    |  1  |  2  |   1    |  0.3    |  5000  |   3000
    0    |  1  |  3  |   1    |  0.5    |  3000  |   1000
    0    |  1  |  5  |   1    |  0.4    |  3800  |   2000
    0    |  1  |  4  |   1    |  0.6    |  8000  |   2800
    0    |  1  |  6  |   1    |  0.7    |  null  |   3000

Rules for getting value from columns:

1- Table C has a higher priority than Table B, that means if both of them has value in the same column, result picking up from Table C, Except when the value is null

2- Result can be GROUP BY on purchase, type, market

3- Result Has FULL JOIN, that means if a row has an equivalent row on another side, use priority for get value, if not whole of row come in result

4- Priority for choose value for columns (rate | max | min):

• If column rate in TABLE C has value, without considering value on TABLE B ==> result pick from TABLE C
• If column rate in TABLE C is null but has value in TABLE B ==> result pick from TABLE B

Answer

This is using sql server syntax, I’m sure you can change as required:

First set up sample data:

declare @a table(purchase int,A_ID int,[type] int,market int,[group] int,rate decimal(5,2),[max] int,[min] int)
insert @a values (1,1,1,1,1,0.12,1000,500)
,(1,1,2,1,1,0.3,2000,1500)
,(0,1,3,1,1,0.2,5000,800)
,(0,1,4,1,1,0.6,8000,2800)
,(0,1,6,1,1,0.7,null,2800)

declare @b table(purchase int,A_ID int,[type] int,market int,[group] int,rate decimal(5,2),[max] int,[min] int)
insert @b values 
(1,1,1,1,null,0.2,null,null)
,(1,1,2,1,null,null,5000,3000)
,(0,1,3,1,null,0.5,3000,1000)
,(0,1,5,1,null,0.4,3800,2000)
,(0,1,6,1,null,null,null,3000)

Then the query:

select coalesce(b.purchase,a.purchase) purchase,
    coalesce(b.A_ID,a.A_ID) A_ID,
    coalesce(b.[type],a.[type]) [type],
    coalesce(b.market,a.market) market,
    coalesce(b.rate,a.rate) rate,
    coalesce(b.[max],a.[max]) [max],
    coalesce(b.[min],a.[min]) [min]
from @a a
full outer join @b b on b.purchase=a.purchase and b.[type]=a.[type] and b.market=a.market
order by rate

Add whatever sorting your require.