MySQL Match...Against vs. nulls?

Question

I have a query that uses left joins like so:

SELECT a.id, a.username, a.date_created, a.contact_id, a.role_id,
  c.first_name, c.middle_initial, c.last_name, c.email, r.role
FROM accounts AS a
LEFT JOIN contacts AS c ON a.contact_id = c.id
LEFT JOIN roles AS r ON a.role_id = r.id
WHERE ( MATCH (a.username, c.first_name, c.middle_initial, c.last_name, c.email) 
  AGAINST ('searchTerm*' IN BOOLEAN MODE) )

Problem is, there are accounts (a.id) that have no corresponding contact (c.id) or role (r.id), so when joined that information comes up null (I'm guessing).

When that happens I get back results that don't match the search term no matter what (on top of the normal results), and they are all the ones that have nulls filled in by the join.

I've tried different kinds of joins, and I've looked on the internet, but I have yet to find something that specifically addresses this problem. I think it will be useful to others as I can see this coming up in other domains.

So, any help would be appreciated, thanks!

Answer 1

You need to replace the NULLS with emtpy strings, then you'll not get any spurious matches. Just match against COALESCE(c.email, '') (etc.), instead of just the field.

COALESCE will return its first parameter, unless it is NULL, then it will return the second parameter.

Answer 2

Hey, guys, I noticed something I thought was an answer but isn't: When I put r.role into the matching clause the problem cleared up. To be honest I don't quite understand the logic but I understand that that's what was making additional records come up because that field was being asked for without a filter.

Unfortunately it remains unsolved. Sorry for the premature answer.