ordering one table by a value from another table, first table has key to second table

[Rosie Fairfield]

Hi,
I am using NetBeans to develop an app to hold the books in my library.

I have one table called Authors that has the first and last name (separate fields) for each author and a integer primary key id.
I have a second table called Books that has the title of the book and an author_id field into the Authors table.

I want to order the books by Author's last name and then title so I have to use the Books table author_id field to get each book's author's last name from the Authors table. I can do this but I don't see how to then sort by last names.

Can I order the Authors table by last name and redo the primary key values so primary key 1 is the first author in the now alphabetized list of authors? then I can order the books by author_id and title.

Any ideas?

[K. Tsang]

You can join the 2 tables together with sql using the author_id as the key. Then order by author last name and book title.

[Roel De Nijs]

Rosie Fairfield wrote:Can I order the Authors table by last name and redo the primary key values so primary key 1 is the first author in the now alphabetized list of authors? then I can order the books by author_id and title.

It's much better to do this in 1 query. So you can join 2 tables and then order the results on a combination of fields from the 1st and 2nd table.

Something like thisSELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.t1_id ORDER BY t1.col3, t2.col1, t2.col4, t1.col1

(PS. This solution is of course not limited to just 2 tables, you can join as many as you want and can order on as many columns as you want.)

Hope it helps!
Kind regards,
Roel

[Rosie Fairfield]

Thank you very much.
This is exactly what I needed, especially the actual sql snippet (saved me having to look up joins). My sql is to basic and/or rusty for me to have thought of joins.

[Roel De Nijs]

Rosie Fairfield wrote:This is exactly what I needed, especially the actual sql snippet (saved me having to look up joins).

Glad to hear I could help!