SQL: Gather right hand values from a join
- by Max Williams
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
