MySQL JOINs with NULL values

Question

I'm trying to do a rather complex (for me, at least) query that involves fetching rows that may have NULL values.

There are four tables here, tags, questions_tags, users_experience, and answers. The connections are rather straightforward. Questions are tagged, tags have names, users give answers to questions, and users have experience with particular tags. I want to find answers that users have given, and their experience (which may be NULL) for that questions' tags. I'm ordering by the number of answers given for a particular tag.

My query is as follows. This is not at all optimized (and if you have optimization suggestions, please suggest away!):

SELECT t.tag_id, t.name, ue.body, COUNT(a.qid)
FROM tags AS t
LEFT JOIN users_experience AS ue
  ON t.tag_id = ue.tag_id
LEFT JOIN questions_tags AS qt
  ON qt.tag_id = t.tag_id
LEFT JOIN answers AS a
  ON a.qid = qt.qid
WHERE a.uid=1
GROUP BY t.tag_id
ORDER BY COUNT(a.qid) DESC;

The problem I'm facing with the above query is that if anyone has noted experience for a particular tag, that will show up for the user whether it is their experience or not. I'd like to see only that particular user's experience, which this query is just not doing. I've run into this problem elsewhere and been stumped, so have had to hack around it.

I tried adding AND ue.uid = 1 to the query, but that will limit the results on only those where the experience has already been given, and not return the NULL values I desire, as well.

Any thoughts on what to do?

Solution

Your AND ue.uid = 1 instinct wasn't wrong, but it belongs in the ON clause so it's part of the JOIN, and it's used to determine which rows are eligible to join.

LEFT JOIN users_experience AS ue
  ON t.tag_id = ue.tag_id AND ue.uid = a.uid

(And that join should then be placed under the answers join.)

Comments

[VoteyDisciple]

You're no longer joining to users_experience on the relevant tags; you're counting all experience in all areas. And there's no need to join to users if the uid value is already available (doing so just adds unnecessary processing).

[Cfreak]

@VoteyDisciple - he wants the user's information where they have experience or not. His original problem stated that when he adds a user ID he didn't get the NULL values. That's because the user ID is null in his user_experience table. I joined tags on users_experience in reverse. It still works. (Note I don't think it's a good idea, I think he should rethink his table design)