SQL: Gather right hand values from a join

Question

Let's say a question has many tags, via a join table called taggings. I do a join thus:

SELECT DISTINCT `questions`.id 
FROM `questions` 
 LEFT OUTER JOIN `taggings` 
  ON `taggings`.taggable_id = `questions`.id 
 LEFT OUTER JOIN `tags` 
  ON `tags`.id = `taggings`.tag_id 

I want to order the results according to a particular tag name, eg 'piano', so that piano is at the top, then by all the other tags in alphabetical order. Currently i'm using this order clause:

ORDER BY (tags.name = 'piano') desc, tags.name

Which is going completely wrong - the first results i get back aren't even tagged with 'piano' at all. I think my problem is that i need to group the tag names somehow and do my ordering test against that: i think that doing it against the straight tags.name isn't working due to the structure of the resultant join table (it does work if i just do a simple select on the tags table) but i can't get my head around how to fix it.

grateful for any advice, max

EDIT - reply to Marcelo re COALESCE Thanks a lot Marcelo - i hadn't seen this before. Must read api's more properly. This does actually help, but only if i select the coalese clause as well. Ie, this:

SELECT DISTINCT `questions`.id 
FROM `questions` 
 LEFT OUTER JOIN `taggings` 
  ON `taggings`.taggable_id = `questions`.id 
 LEFT OUTER JOIN `tags` 
  ON `tags`.id = `taggings`.tag_id 
 ORDER BY (COALESCE(tags.name,'') = 'piano') desc, tags.name

still gives spurious results. However, this:

SELECT DISTINCT `questions`.id, COALESCE(tags.name,'')
FROM `questions` 
 LEFT OUTER JOIN `taggings` 
  ON `taggings`.taggable_id = `questions`.id 
 LEFT OUTER JOIN `tags` 
  ON `tags`.id = `taggings`.tag_id 
 ORDER BY (COALESCE(tags.name,'') = 'piano') desc, tags.name

returns the correct results. I'd like to still just select the question ids though. Definitely getting closer anyway...

Answer 1

Maybe because tags.name = 'piano' evaluates to NULL when tags.name is NULL. Try COALESCE(tags.name, '') = 'piano'.