MySQL inner join on two columns

I have two tables, books and authors. books has a author_id column and a secondary_author_id column (no books have more than two authors). I'm so far doing:

SELECT * FROM books
LEFT JOIN authors
ON books.author_id=authors.id

which is handling开发者_运维知识库 the join with the first author. I can't work out how I'd handle the secondary author though. Should I change my schema, or do I just need a bit of SQL help?

SELECT       books.*, author1.*, author2.*
FROM         books
  LEFT JOIN  author AS author1
  ON         author1.author_id = books.author_id
  LEFT JOIN  author AS author2
  ON         author2.author_id = books.secondary_author_id

In SQL, you can alias the tables by adding it after the table name. Just be careful, now you'll have duplicate columns, so instead of author1.* you will probably want to alias the results of both author1 and author2.

EDIT

Additional details -- Say you have your basic table (i'll include the details so if people want to test on their own they can):

CREATE DATABASE test;
USE test;

CREATE TABLE books
(
  book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50),
  author_id INT NOT NULL,
  secondary_author_id INT
);

CREATE TABLE authors
(
  author_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO authors (author_id,name) VALUES (1,'Sue Z. Que'),(2,'John Doe'),(3,'Bob Smith');
INSERT INTO books (book_id,title,author_id,secondary_author_id) VALUES (1,'JOIN-ing Two Tables',1,2);

If you do the select I mention above, your result will be the following:

|----------------------- books TABLE -----------------------------|---- authors table -----|---- authors table ---|
+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+
| book_id | title               | author_id | secondary_author_id | author_id | name       | author_id | name     |
+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+
|       1 | JOIN-ing Two Tables |         1 |                   2 |         1 | Sue Z. Que |         2 | John Doe |
+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+

(I've added the top header just for calrity's sake) you see you have two author_id's and two name's (as they are joins of the same table and same column names). BUT, if you alias the columns from the joins like so:

SELECT       books.*, author1.name AS primary_author, author2.name AS secondary_author
FROM         books
  LEFT JOIN  authors AS author1
  ON         author1.author_id = books.author_id
  LEFT JOIN  authors AS author2
  ON         author2.author_id = books.secondary_author_id;

You get a much cleaner result:

|----------------------- books TABLE -----------------------------| authors table -|- authors table --|
+---------+---------------------+-----------+---------------------+----------------+------------------+
| book_id | title               | author_id | secondary_author_id | primary_author | secondary_author |
+---------+---------------------+-----------+---------------------+----------------+------------------+
|       1 | JOIN-ing Two Tables |         1 |                   2 | Sue Z. Que     | John Doe         |
+---------+---------------------+-----------+---------------------+----------------+------------------+

SELECT books.* FROM books, authors.name, secondary_authors.name
LEFT JOIN authors
ON books.author_id=authors.id
LEFT JOIN authors as secondary_authors
ON books.secondary_author_id=secondary_authors.id

You need to rethink your design, because one day there will be a book with three authors, and the next day there will be a book with zero. (I've been there myself.)

Edit

As your comment says: yes, you need a books_authors table. As long as you have your indexes set up properly, it's not a big performance hit.

The most annoying part is that you're often going to want to string the authors together (one entry per book, concatenating all the authors into a single column). You'll probably end up creating a view for that.

just do another join on the secondary id