SQL join two tables with specific condition

join, left-join, select, sql, where-clause

Solution

You need to move the `type = 2` filter to the join condition:

SELECT  TableA.RecordID, TableB.Text 
FROM    TableA 
        LEFT JOIN TableB 
            ON TableA.RelationID = TableB.TableARelationID 
            AND TableB.Type = 2;

Consider the result of just this:

SELECT  TableA.RecordID, TableB.Text, TableB.Type
FROM    TableA 
        LEFT JOIN TableB 
            ON TableA.RelationID = TableB.TableARelationID;

You would get

RecordID | Text | Type
  1      | NULL | NULL
  2      |   B  |  2
  3      |   C  |  2
  4      |   D  |  2

Then you are filtering on the type column, so for recordID = 1 you have where `NULL = 2` which is false (it is not actually false, it is null, but it is not true), so this record is elimitated from the final result.

Whenever you left join you must apply any filtering criteria you wish to apply to the left table in the join condition not the where, otherwise you effectively turn it into an inner join.

Problem

Table A structure: Table B structure: Above are two tables, TableB.TableARelationID is a relationID which used to map table A. Desired output: The desired result would be taking TableA.RecordID and TableB.Text, but only of Type 2 in table B, i.e. ignore Type 1 Below is the SQL query which I used: ``` SELECT tablea.recordid, tableb.text FROM tablea LEFT JOIN tableb ON tablea.relationid = tableb.tablearelationid WHERE type = 2 ``` But the above query would output: i.e. RecordID 1 was missing, as the "where" clause filtered. So how can I show RecordID 1 from Table A?

Original source