SQL Server query - return null value if no match

join, sql, sql-server

Solution

Move the condition for `table2` out of your `WHERE` clause and into your `JOIN`.

SELECT 
    Table1.IDField, Table2.IDField
FROM 
    Table1
LEFT OUTER JOIN Table2 
    ON Table1.PersonID = Table2.PersonID
    AND Table2.Category = 'Foo'
WHERE 
    Table1.IDField = '12345'

Problem

I'm running into a problem that I cannot figure out for some reason. I am trying to write a query that joins two tables, in which a match may not be found in a table. Such as: ``` SELECT Table1.IDField, Table2.IDField FROM Table1 LEFT OUTER JOIN Table2 ON Table1.PersonID = Table2.PersonID WHERE (Table1.IDField = '12345') AND (Table2.Category = 'Foo') ``` If there is no match in `Table2`, it's not returning anything. However, I need it to just return a NULL for that column if there is no match and still return the value from `Table1`. I have changed up the `JOIN` with everything that I can think of, but to no avail. `Table2.Category` can contain multiple other values, so doing a `OR IS NULL` type of deal won't work. So, if there is no match for `Table2.Category = 'Foo'`, I am still needing it to return: ``` Table1 | Table2 ---------------- 12345 | NULL ``` Any suggestions?

Original source