• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rosie Fairfield
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 this

(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
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic