CODEWITHSUNDEEP
sql-serverjoin
K09
K09

Reputation: 201

Join Based On Column Value (Best Match)

I have 3 columns to base my JOIN on -> ID, Account, Cust. There can be multiple rows containing the same ID value.

I want to prioritise my JOIN on 1) ID, 2) Account, 3) Cust.

So in the example below, the UserCode that should be populated in @UserData should be 'u11z' as all columns contain a value.

How do I do this? Below my code to date...

UPDATE  @UserData
    SET UserCode = ur.UserCode
FROM @UserData uA 
INNER JOIN UserReference ur
    ON uA.ID = ur.ID
    AND ((ua.Account = ur.Account) OR (ur.Account = ur.Account))
    AND ((ua.Cust = ur.Cust) OR (ur.Cust = ur.Cust))

UserReference TABLE:

Cust       Account      ID       UserCode
234         NULL      9A2346     u12x
234         Test      9A2346     u11z
NULL        NULL      9A2346     u30s

@UserData TABLE:

Cust       Account      ID       UserCode
234         Test      9A2346     NULL

Thanks!

Upvotes: 0

Views: 1052

Answers (3)

Hannah Vernon
Hannah Vernon

Reputation: 3472

Is this what you want? It is difficult to understand what you are asking for.

USE tempdb;

CREATE TABLE UserReference
(
    ID VARCHAR(255) NULL
    , Account VARCHAR(255) NULL
    , Cust INT NULL
    , UserCode VARCHAR(255)
);

INSERT INTO UserReference VALUES ('9A2346', NULL, 234, 'A');
INSERT INTO UserReference VALUES ('9A2346', 'TEST', 234, 'B');
INSERT INTO UserReference VALUES ('9A2346', NULL, NULL, 'C');

DECLARE @UserData TABLE 
(
    ID VARCHAR(255) NULL
    , Account VARCHAR(255) NULL
    , Cust INT NULL
    , UserCode VARCHAR(255)
);

INSERT INTO @UserData
SELECT UR.ID, UR.Account, UR.Cust, NULL
FROM dbo.UserReference UR;

UPDATE  @UserData
    SET UserCode = ur.UserCode
FROM @UserData uA 
INNER JOIN UserReference ur
    ON uA.ID = ur.ID
    AND ua.Account = ur.Account
    AND ua.Cust = ur.Cust;


SELECT *
FROM @UserData;

Results of the last SELECT :

enter image description here

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

You can try the following. I joined tables, counted the number of matches, and ranked them. Then select rank 1.

; with userCte (userCodeA, userCodeB, rank)
as
(
select a.usercode, b.usercode, 
rank() over (partition by a.id order by case when a.cust = b.cust then 1 else 0 end +
case when a.account = b.account then 1 else 0 end  +
case when a.id = b.id then 1 else 0 end desc) as rank
from userdata a
join userreference b
on a.id = b.id or a.account = b.account or a.id = b.id
)

select * from userCte
--update userCte
--set userCodeA = userCodeB
where rank = 1

Upvotes: 1

Sai
Sai

Reputation: 1

If I understood your question correctly...

And if any col in a row having a null value will drop the priority then you can use a query something like below to check the count of null values in a row, this might not be a complete answer, but a possible approach...

SELECT count(*) FROM TableName WHERE Col1 IS NULL and Col2 is null

Upvotes: 0

Related Questions