SQL Query help - If Mathcing record does not exist in table then look in another table

SQL 321 Reputation points
2022-10-04T14:04:39.663+00:00

Hi:

Need help with SQL. I have this SQL which joins #tblA and #tblB. What I need is that if #tblB does not have a matching record then it should pull that information from #tblC. 

CREATE TABLE #tblA  
(MBRID INT,  
 ACTIVEID VARCHAR(10),  
 GRPNAME VARCHAR(10))  
  
CREATE TABLE #tblB  
(MBRID INT,  
 POLID VARCHAR(10),  
 PRSNID INT,  
 ACTCD VARCHAR(10))  
  
CREATE TABLE #tblC  
(MBRID INT,  
 POLID VARCHAR(10),  
 PRSNID INT,  
 ACTCD VARCHAR(10))  
  
INSERT INTO #tblA VALUES (111,'EARNED','EXCHANGE1')  
INSERT INTO #tblA VALUES (111,'EARNED','EXCHANGE1')  
INSERT INTO #tblA VALUES (112,'PAYMENT','EXCHANGE2')  
INSERT INTO #tblA VALUES (113,'PAYMENT','EXCHANGE2')  
INSERT INTO #tblA VALUES (114,'EARNED','EXCHANGE3')  
INSERT INTO #tblA VALUES (115,'EARNED','EXCHANGE4')  
  
INSERT INTO #tblB VALUES (111,'PAYMENT',222,'PCH')  
INSERT INTO #tblB VALUES (112,'EARNED',333,'ABC')  
INSERT INTO #tblB VALUES (112,'EARNED',333,'PQR')  
INSERT INTO #tblB VALUES (113,'EARNED',555,'PCH')  
INSERT INTO #tblB VALUES (113,'EARNED',555,'XYZ')  
  
  
INSERT INTO #tblC VALUES (111,'PAYMENT',222,'PCH')  
INSERT INTO #tblC VALUES (112,'EARNED',333,'ABC')  
INSERT INTO #tblC VALUES (112,NULL,333,'PQR')  
INSERT INTO #tblC VALUES (114,NULL,444,'ABC')  
INSERT INTO #tblC VALUES (115,'PAYMENT',555,'ABC')  
  
SELECT DISTINCT A.MBRID, B.POLID, B.PRSNID   
FROM (SELECT MBRID, ACTIVEID, GRPNAME FROM #tblA) A  
LEFT JOIN #tblB B  
ON A.MBRID = B.MBRID

EXPECTED OUTPUT:

247368-expectedoutput.jpg

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2022-10-04T14:32:57.35+00:00 
     SELECT  Distinct   
 A.MBRID, B.POLID, Coalesce(B.PRSNID,C.PRSNID)  PRSNID  
 FROM #tblA  A  
 LEFT JOIN #tblB B    
 ON A.MBRID = B.MBRID  
 LEFT JOIN #tblC C    
 ON A.MBRID = C.MBRID
    0 comments
  2. CosmogHong-MSFT 22,781 Reputation points Microsoft Vendor
    2022-10-05T01:29:32.037+00:00

    Hi @SQL
    You could use COALESCE function or ISNULL function to solve this issue.
    Check this: 

    --Use COALESCE function  
SELECT Distinct A.MBRID,COALESCE(B.POLID,C.POLID) AS POLID,COALESCE(B.PRSNID,C.PRSNID) AS PRSNID  
FROM #tblA A LEFT JOIN #tblB B ON A.MBRID = B.MBRID  
             LEFT JOIN #tblC C ON A.MBRID = C.MBRID  
  
--Use ISNULL function  
SELECT Distinct A.MBRID,ISNULL(B.POLID,C.POLID) AS POLID,ISNULL(B.PRSNID,C.PRSNID) AS PRSNID  
FROM #tblA A LEFT JOIN #tblB B ON A.MBRID = B.MBRID  
             LEFT JOIN #tblC C ON A.MBRID = C.MBRID

    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.

    0 comments