SQL Server query - return null value if no match

Question

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?

Using WHERE Table1.IDField = '12345' AND (Table2.Category = 'Foo' OR Table2.Category IS NULL) ought to work exactly the way you describe it — marc_s
– marc_s, Commented Jan 4, 2013 at 16:30
@marc_s - whenever I modify that WHERE clause to do that, it is not returning any rows at all. I'm running this query in MS SQL Server Management Studio. — BrewingDev
– BrewingDev, Commented Jan 4, 2013 at 16:38

Michael Fredrickson · Accepted Answer · 2013-01-04 16:33:13Z

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'

Melanie · Accepted Answer · 2013-01-04 16:32:26Z

Try this:

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

then delete the 'Foo' line from the WHERE clause

bejaysea · Accepted Answer · 2013-01-04 16:34:43Z

The problem isn't in the join per se, but in the requirement in the where clause that match table2. I've solved this with a where coalese(table2.category, 'Foo') = 'Foo'. That way if the table2 is null, it will still match.

Collectives™ on Stack Overflow

SQL Server query - return null value if no match

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related