Join in SQL with Duplicate Key

Question

Hi All, Lets say I have table like below. I do have the duplicate data as per shown below. When I joining using on QUOKEY, I didn't get the expected result like shown below. How can I get the expected result like below? This is part of my SSIS project.

SQL Version:

Table:

DDL and Sample Data Population:

   -- DDL and sample data population, start  
   DECLARE @tbl TABLE (QUOKEY varchar(50), VEHNO varchar(50), CLS varchar(50))  
   INSERT INTO @tbl (QUOKEY, VEHNO, CLS) VALUES  
   ('91TMIQUO5750161','WGE223','CA'),  
   ('91TMIQUO3338345','PKQ902','PC'),  
   ('91TMIQUO3338345','PKQ902','PC'),  
   ('91TMIQUO3660377','CCQ2927','PC'),  
   ('91TMIQUO3660377','CCQ2927','PC')  
  
      DECLARE @tbl2 TABLE (QUOKEY varchar(50), DT_UKEY varchar(50))  
   INSERT INTO @tbl2 (QUOKEY,DT_UKEY) VALUES  
   ('91TMIQUO5750161','9102413820220512151552306'),  
   ('91TMIQUO3338345','9102278620220517135729342'),  
   ('91TMIQUO3338345','9102278620220517135729342'),  
   ('91TMIQUO3660377','9102630020220527111614201'),  
   ('91TMIQUO3660377','9102630020220527111614201')  
  
   SELECT *  
  FROM @tbl a    
  INNER JOIN @tbl2 b ON a.QUOKEY=b.QUOKEY  
  
   -- DDL and sample data population, end  

Answer 1

Hi @jn93
Try this query:

SELECT *  
FROM @tbl a INNER JOIN (SELECT DISTINCT * FROM @tbl2) b ON a.QUOKEY=b.QUOKEY  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check a query:

;   
with A as  
(  
    select *, row_number() over (partition by QUOKEY order by QUOKEY) as n  
    from @tbl  
),  
B as  
(  
    select *, row_number() over (partition by QUOKEY order by QUOKEY) as n  
    from @tbl2  
)  
select a.QUOKEY, a.VEHNO, a.CLS, b.DT_UKEY  
from A  
inner join B on B.QUOKEY = A.QUOKEY and B.n = A.n  
order by A.CLS, A.QUOKEY