select from normalized tables using external references

Question

I am trying to create a normalized set of tables for my books and then to select them ordering by either book title or authors. I want to be able to have 'n' books per author, and 'n' authors per book.

The problem I want to solve is how to display my books and authors ordered by tile or by lastname,firstname,middlename?

I started with a table like this with some 1441 entries.

create table books(
bookid serial,
title text,
firstname text,
lastname text);

I then created an authors table

create table authors(
authorid serial,
firstname text,
lastname text);

and populated it.

I then created a cross reference table

create table bookAuthor
(    
     bookId INTEGER NOT NULL REFERENCES books(bookId),
     authorId INTEGER NOT NULL REFERENCES authors(authorId)
);

and

create unique index bookAuthor_unique_index on bookAuthor(bookId, authorId);

I then populated the bookauthor table with 1441 entries.

I am pretty sure the three tables are populated correctly. I managed to do several inserts into the authors table and then insert the correct cross relationshipes into the bookauthor table.

I am now stuck, I can't figure out how to display my books and authors ordered by title or by authors names.

Am I going down the wrong path to create this ability to create N titles per author and N authors per book.

I did multiple searches for foreign keys, and multiple tables with nothing that seemed to resolve my problem.

I'm in a postgresql 9.x environment.

Answer 1

A join will be helpful.

select * from bookAuthor
inner join books on bookAuthor.bookId = books.bookid
inner join authors on bookAuthor.authorId = authors.authorid
order by books.title;

Or you can order by authors.lastname, authors.firstname instead.