Join on to table with multiple matches but only bring back specified match

Question

I have 3 tables.
Table A contains the data that my query is going to be based on. It contains id1. Table B is my connecting table, it contains 2 columns that I'm concerned with: id1 & id2.

Table C is my lookup table, it contains id2 and ReasonName. I need the column ‘ReasonName’ and all the data from table A.

If I do the following SQL:

 SELECT NAME, 
       STARTDATE, 
       ENDDATE, 
       REASON, 
       ID1 
FROM   TABLEA 
       LEFT JOIN TABLEB 
              ON TABLEA.ID1 = TABLEB.ID1

I am then able to do a second join on to TableC, however this is where the problem lies. There are multiple matches in TableC so therefore my totalrows increases. I need to avoid this. See image for what TableC looks like:

If my SQL statement now looks like this:

 SELECT TABLEA.NAME, 
       TABLEA.STARTDATE, 
       TABLEA.ENDDATE, 
       TABLEA.REASON, 
       TABLEA.ID1, 
       TABLEC.REASONNAME 
FROM   TABLEA 
       LEFT JOIN TABLEB 
              ON TABLEA.ID1 = TABLEB.ID1 
       LEFT JOIN TABLEC 
              ON TABLEB.ID2 = TABLEC.ID2 

Then my rows increase by around 1000. This is because it is quite possible that TableA.id1 matches multiple id2’s found in TableC. It then duplicates rows apart from the different TableC.ReasonName.

In TableC there is one ReasonName that I am concerned with. For this example I will say it is ‘reasonf’.

What I need is to bring through ReasonName from TableC, BUT only the ones that contain reasonf, the rest I want to say NULL or to do a COALESCE with ‘No Reason’. I have tried putting a WHERE clause into the statement. See SQL:

 SELECT TABLEA.NAME, 
       TABLEA.STARTDATE, 
       TABLEA.ENDDATE, 
       TABLEA.REASON, 
       TABLEA.ID1, 
       TABLEC.REASONNAME 
FROM   TABLEA 
       LEFT JOIN TABLEB 
              ON TABLEA.ID1 = TABLEB.ID1 
       LEFT JOIN TABLEC 
              ON TABLEB.ID2 = TABLEC.ID2 
WHERE  TABLEC.ID2 = 'asd1f5as98a4' 

But then it will only bring though those records where there is a match with ‘reasonf’, I want it to display reasonf people and ignore the rest (leave as null or something) so I have no duplicates but my full result set.

I'm thinking I may need to change to a right join or possibly change the WHERE but I'm not entirely sure.

Solution 1

Move your WHERE clause into the join...

select 
  TableA.name,
  TableA.startDate,
  TableA.endDate,
  TableA.Reason,
  TableA.id1,
  COALESCE(TableC.ReasonName, 'No Reason') AS FilteredReasonName
from
  TableA
left join
  TableB
    on  TableA.id1 = TableB.id1
    and TableB.id2 = 'asd1f5as98a4'
left join
  TableC
    on  TableB.id2 = TableC.id2

Now, TableB (the source of the duplication) only joins if it is ReasonF or not at all. Then the next join goes and looks up the ReasonName for that code.

If nothing is found, the COALESCE() replaces the NULL with 'No Reason'.

Solution 2

If I'm understanding what you want correctly....

select 
    TableA.name,
    TableA.startDate,
    TableA.endDate,
    TableA.Reason,
    TableA.id1,
    TableC.ReasonName
from
TableA
left join TableB on TableA.id1 = TableB.id1
left join TableC on TableB.id2 = TableC.id2 and TableC.id2 = 'asd1f5as98a4'