Ordering the results from a junction table based on values of the row its foreign key belongs to

Question

I have three tables for a many-to-many: Authors, Authorships, Books. I would like to select rows from authorship and order it alphabetically according to the author this row belongs to. Example:

-- Authors --
ID   Name
1    Peter
2    Gregory
3    Daniel

-- Authorships--
ID   AuthorId    BookId
1    1           1
2    2           1
3    3           1

-- Books--
ID   Name
1    Foobook

I would like to write a select statement that returns all rows from authorship belonging to a specific book then orders the result by author name.

So something like this:

SELECT * FROM Authorships WHERE BookId = 1 ORDER BY (Authors.Name???);

Except I need to order the result. I understand how this question might look silly because of its workaround/inefficient nature, but I am working with a lot of legacy code and am not allowed to really change anything else.

Thank you.

Answer 1

This will work:

SELECT auth.* FROM Authorships auth, Authors au, Books bk WHERE auth.BookId = bk.ID and auth.AuthorId = au.ID ORDER BY au.Name

SQLFiddle Link: SQLFiddle