Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server query - return null value if no match

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?

like image 764
BrewingDev Avatar asked Jan 04 '13 16:01

BrewingDev


2 Answers

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'
like image 200
Michael Fredrickson Avatar answered Nov 18 '22 11:11

Michael Fredrickson


Try this:

LEFT OUTER JOIN
Table2 ON Table1.PesonID = Table2.PersonID
AND Table2.Category = 'Foo'

then delete the 'Foo' line from the WHERE clause

like image 38
Melanie Avatar answered Nov 18 '22 12:11

Melanie