Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

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

 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
The longest recorded flight time of a chicken is 13 seconds. But that was done without this tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic