I have a table called Diag that has these columns in SQL Server:

ICD9_L2  RepID
----------------
A         1
D         1
H         1
B         2
D         2
H         2
J         2
A         3
B         3

I have another table called C that has similar structure different column names:

ICD9     n
----------------
A         1
B         1
C         1
D         1
E         1
A         2
B         2
C         2
D         2

I want to join both tables considering the following

• I want C.ICD to match exactly Diag.ICD_L2 so if the Diag.ICD_L2 not in C.ICD, then I do not want it returned (i.e. INNER JOIN)

• but for Diag.RepID even if it does not exist C.N, then I want this row to show as NULL (i.e LEFT OUTER JOIN)

I tried this

SELECT * 
FROM C 
LEFT OUTER JOIN Diag ON C.ICD = Diag.ICD_L2 
                     AND C.N = Diag.RepID

but this returns more rows than I want as it included Diag.ICD_L2 that are not in C.ICD too.

I also tried

SELECT * 
FROM C 
INNER JOIN Diag ON C.ICD = Diag.ICD_L2 
                AND C.N = Diag.RepID

That just showed shared rows only.

How can I have a join half of it inner and the other half is left outer?

CodePudding user response：

You need two queries

1. Select the matches (inner join)

2. Select C rows that don't match (difference)

3. Then union them together

   SELECT IC09, n, RepID
    FROM C 
    INNER JOIN Diag ON C.ICD = Diag.ICD_L2 
                    AND C.N = Diag.RepID
    UNION
    SELECT IC09, n, null as RepId
    FROM C 
    LEFT OUTER JOIN Diag ON C.ICD = Diag.ICD_L2 
                         AND C.N = Diag.RepID
    WHERE 
    Diag.RepID IS NULL
   

CodePudding user response：

You want all the rows from DIAG so just start with DIAG and LEFT JOIN

SELECT *
FROM DIAG
LEFT JOIN C ON C.ID = DIAG.ICD_L2 AND C.N = DIAG.REPID