I have below 2 tables:

Table_1

MappingTable

    CREATE TABLE Table1
    ([Col1] varchar(3), [Col2] varchar(2), [Col3] varchar(5))
;
    
INSERT INTO Table1
    ([Col1], [Col2], [Col3])
VALUES
    ('Pr1', 'C1', 'TypeA'),
    ('Pr2', 'C2', 'TypeB'),
    ('Pr3', 'C3', 'TypeC'),
    ('Pr4', 'C4', 'TypeA')
;


CREATE TABLE MappingTable
    ([Col4] int, [lookupCol2] varchar(4), [lookupCol3] varchar(5), [Col5] varchar(3))
;
    
INSERT INTO MappingTable
    ([Col4], [lookupCol2], [lookupCol3], [Col5])
VALUES
    (1111, 'C1', 'TypeA', 'Yes'),
    (1111, NULL, 'TypeA', 'No'),
    (1111, NULL, 'TypeB', 'No'),
    (1111, NULL, 'TypeC', 'No'),        
    (2222, 'C1', 'TypeA', 'Yes'),
    (2222, 'C2', 'TypeA', 'Yes'),
    (2222, NULL, 'TypeA', 'No'),
    (2222, NULL, 'TypeB', 'No'),
    (2222, NULL, 'TypeC', 'No')
;

I have written below Query which is generating incorrect data:
select a.col1, a.col2, a.col3, b.col4, b.col5 
from Table1 A
inner join
  MappingTable B
   on a.Col3  = b.lookupcol3
   and a.Col2 = case when b.lookupcol2 is null then a.col2 else b.lookupcol2 end

Screenshot of incorrect data: Desired Result

The requirement is to fetch the mapping data for every Col1 record from the mapping table. Concern: The Table1 key columns Col2 and Col3 has a mapping record for Pr1 in mapping table, so the matched record can be pulled directly but the key columns for Pr4 and Pr5 are not matching for Col2. In the mapping table, If the lookupcol2 is blank for any Col4 and lookupCol3 combination then it is a standard record, so in the Pr4 and Pr5 case, the query should fetch the standard record where lookupCol2 is blank.

Am not sure what am I missing here.

CodePudding user response：

You can use a dense_rank solution to flag up results which have a specific record and a standard record and filter them out.

declare @Table_1 table (Col1 varchar(3), Col2 varchar(2), Col3 varchar(6));

insert into @Table_1 (Col1, Col2,   Col3)
values
('Pr1', 'C1', 'Type A'),
('Pr2', 'C2', 'Type B'),
('Pr3', 'C3', 'Type C'),
('Pr4', 'C4', 'Type A'),
('Pr5', 'C5', 'Type A');

declare @MappingTable table (Col4 varchar(4), LookupCol2 varchar(6), LookupCol3 varchar(6), Col5 bit);

insert into @MappingTable (Col4, LookupCol2, LookupCol3, Col5)
values
('1111', 'C1', 'Type A', 1),
('1111', '', 'Type A', 0),
('1111', '', 'Type B', 0),
('1111', '', 'Type C', 0),
('2222', 'C1', 'Type A', 1),
('2222', 'C2', 'Type A', 1),
('2222', '', 'Type A', 0),
('2222', '', 'Type B', 0),
('2222', '', 'Type C', 0);

with cte as (
    select A.Col1, A.Col2, A.Col3, B.Col4, B.Col5
        -- If a specific and standard record exists this will be 2 for the standard record
        , dense_rank() over (partition by A.Col1, A.Col2, A.Col3 order by B.LookupCol2 desc) rn
    from @Table_1 A
    inner join @MappingTable B on A.Col3  = B.LookupCol3
        and A.Col2 = case when B.LookupCol2 = '' then A.Col2 else B.LookupCol2 end
)
select Col1, Col2, Col3, Col4, Col5
from cte
-- Filter out the standard records when a specific record exists
-- Comment out the following line and add rn to the output to understand how it works
where rn = 1
order by Col1, Col2;

Returns:

Note the DDL DML which if you add to your questions makes them much easier to answer (because we don't have to type it all in).