I have the products table as follows:
ProductID | ItemId | Color
---------------------------
1 X 5N
2 X 10N
3 X 2N
4 Y 12S
and a second one joined by ItemId
ID | ItemId | Group | Color | Value
-------------------------------------
1 X T null 5
2 X T 5N 7
3 X M 2N 9
4 X M null 6
5 Y M null 12
I'd like to write a select that will return distinct Group values. As you can see there two groups with T value and ItemId is X, so in this case if color is the same in product and in other table then value with same color must be used.
If there is no such color, then take null value. What is important is that all distinct groups for each product must be fetched. The only thing is the priority for the same groups if color match.
Desired result:
ProductID | ItemId | Group | Color | Value
-------------------------------------------
1 X T 5N 7
1 X M 5N 6
2 X T 10N 5
2 X M 10N 6
3 X T 2N 5
3 X M 2N 9
4 Y M 12S 12
DDL:
create table ProductPrices
(
Id bigint not null
constraint pk_product_prices
primary key,
ItemId varchar(50) not null,
Group varchar(10) not null,
Value decimal(18,4) not null,
Color varchar(50)
)
go
create table Products
(
ProductId nvarchar(70) not null
constraint PK_table_4
primary key,
ItemId nvarchar(50) not null,
Color nvarchar(50) not null
)
go
Best Answer
Solution 1
The idea is to find distinct groups for each product, then the highest priority match calculated using a
CASE
expression:Solution 2
This joins the tables in the natural way, then uses a window function to find the highest priority match for each group. This code only accesses each base table once:
Solution 3
This is the same logic as solution 2, but uses a subquery and a more compact syntax:
Solution 4
With the following index on
dbo.ProductPrices
:The following solution can perform well:
Demos
db<>fiddle demo
As a side note,
ItemId
is defined asnvarchar(50)
in one table, andvarchar(50)
in the other. Same with theColor
column. These problems should be fixed, if possible.