C# / SQL Determine matching score based on properties

February 9, 2023

On a project where we have SQL tables called Products and Conditions, we want to determine which product belongs to which most matching condition, because a product can belong to multiple conditions.

Is there a way to do this in C# or SQL?

Below you can find a shorted version of the tables with the properties that we want to match on:

CREATE TABLE Products
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Property1] SMALLINT NULL,
    [Property2] SMALLINT NULL,
    [Property3] NVARCHAR(20) NULL,
    [Property4] NVARCHAR(20) NULL
)

CREATE TABLE Conditions
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Property1] SMALLINT NULL,
    [Property2] SMALLINT NULL,
    [Property3] NVARCHAR(20) NULL,
    [Property4] NVARCHAR(20) NULL
)

As a result we want for each product the conditions and sorted by most matching score based on the 4 properties.

Because we have 4 properties, the resulting score could be 0 / 25 / 50 / 75 / 100.

>Solution :

In sql you can join the two tables on matching properties and use iif method to compute the total score and order the results by the total score like below :


Select * from (
Select p.*, c.*,
 iif([Property1] = p.[Property1],25,0) +
 iif([Property2] = p.[Property2],25,0) +
 iif([Property3] = p.[Property3],25,0) +
 iif([Property4] = p.[Property4],25,0) [TotalScore]

 from Products p inner join Conditions c 
      on c.[Property1] = p.[Property1] or 
         c.[Property2] = p.[Property2] or  
         c.[Property3] = p.[Property3] or  
         c.[Property4] = p.[Property4]) q
order by TotalScore desc