How can I sort one table by a column in another in MySQL?

Question

Say I have a table named items:

id      int(11)
...
tag     int(11)

And another table named tags:

id      int(11)
name    varchar(255)

The tag column in items is an index into the tags table. How can I select some rows from the items table and sort the results by the name column in tags?

Answer 1

You can join the tables but you do not have to select the name column from the tags table.

 select a.id, a.tag
 from items a join tags b on a.tag = b.id
 order by b.name

This way you are selecting items sorted by the tag name.

Answer 2

SELECT items.id, items.tag, tags.name FROM items LEFT JOIN tags on items.id = tags.id ORDER BY tags.name